SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-56
Types of Join Queries
Notice also that the ON clause specifies the join conditions. In a left outer join query,
the WHERE clause applies restrictions on the result of the join operation. To list
employees who do not have departments, you could check for the occurrence of a null
value in the DEPT_NUM column of the DEPT table as follows:
SELECT S.EMP_NAME
FROM SALESEMP S LEFT JOIN
DEPT DT
ON S.DEPT_NUM = DT.DEPT_NUM
WHERE DT.DEPT_NUM IS NULL;
The IS NULL predicate applies to the DEPTNUM column of the DEPT table because it
appears in the join predicate and belongs to the table that is not to be preserved.
Because of these two reasons, a null value is guaranteed to appear in the DEPTNUM
columns of the result whenever SQL finds that, for a given EMPLOYEE.DEPTNUM
value, a matching DEPT.DEPTNUM value does not exist.
How a Left Join Preserves Data
SQL preserves data from the table on the left of the keywords LEFT JOIN. So, in the
preceding example, data from the SALESEMP table is preserved.
To preserve rows from the SALESEMP table, SQL extends each row that does not
satisfy the search condition with as many null values as there are columns from the
table on the right in the result. Such a row is called a null-augmented row. The row is
added to the result after extending or augmenting it with null values.
Note that the null values only appear in those columns that belong to the table that
appears on the right of the keywords LEFT JOIN (in the preceding example, the
ORDERS table).
Predicates that involve columns from a table that appears on the right of the keywords
LEFT JOIN are evaluated after the left join is performed: that is, only after null
augmentation is performed.