# Existence Checking for Structured Data

## 1. Existence checks of foreign key mapping

Find records according to the existence of foreign key mapping based on two associated tables.

【Example 1】 Based on the following Score table and Student table, calculate the average score of each boy in class one.

【Analysis】

When retrieving data from Score table, we check whether the current record is about a boy in class one. Select it out if it is.

【SPL script】

A6’s result:

When the foreign key table is too large to fit into memory, we can use cursor to retrieve data from source tables and then perform order-based merge between cursors.

【Example 2】 Based on the following are Order table and Detail table, find the number of orders that don’t use the discount in each month of the year 2014.

【Analysis】

When retrieving data from the Order table, we check whether the discount in the current record is 0 and select it out if it is.

【SPL script】

A6’s result:

## 2. Checks through non-equi join

We search for data in a table according to whether a specific condition is met through the non-equi join.

【Example 3】 Based on the following order data, we want to find the amount of orders that span over a year. Below is part of the source data:

【Analysis】

When retrieving data from the Order table, we check whether there are any records whose order span over a year and select them out if there is.

【SPL script】

A5’s result:

## 3. Non-existence checks of foreign key mapping

Find records according to the non-existence of foreign key mapping based on two associated tables.

【Example 4】Based on the following Student table and Score table, find students who get scores above 80 for all subjects:

【Analysis】

When retrieving data from the Student table, we check whether there is a score below 80 in the current record and, if it doesn’t have any, select it out.

【SPL script】

A6’s result:

## 4. Checks through double negatives

Find matching records through double negaives.

【Example 5】 Based on the following Student table, SelectCourse table and Course table, find students who select all courses.

【Analysis】

When retrieving records from the Student table, we check whether there is any course the current student doesn’t select and, if there isn’t one, select it out. We can also think the opposite in the positive way to select the records where the number of selected courses is equal to that of all courses.

【SPL script】

A7’s result:

## 5. Checks of existence of any values meeting a specific condition

Find records according to the existence of any two values that meet a specific condition based on two associated tables.

【Example 6】 Based on the following Student table and Score table, find Student records where there are any two scores whose difference is over 30.

【Analysis】

When retrieving records from the Student table, we check if there are records where scores of any subjects have a 30 difference between them, and select it out if there are.

【SPL script】

A7’s result:

## 6. Checks of all eligible members meeting a specific condition

Find all records meeting a specific condition from a data table.

【Example 7】Based on the following Employee table, find the employees whose salaries are higher than those of all salespeople. Below is part of the source data:

【Analysis】

When retrieving data from the Employee table, we check whether the current employee’s salary is higher than those of all salespeople, and select it out if it is.

【SPL script】

A4’s result:

Read SPL CookBook to find more related examples.