SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
J-3
Examples - Joins
DEPT table contain a question mark (?) to denote missing information. Each
question mark represents a null value.
SELECT E.LAST_NAME, E.FIRST_NAME, E.DEPT_NUM,
D.DEPT_NUM, D.DEPT_NAME
FROM EMPLOYEE E LEFT JOIN DEPT D
ON E.DEPT_NUM = D.DEPT_NUM
The query returns this data:
In an outer join, the WHERE clause restricts the result. For example, to get rows
related only to employees who are the exceptions, check for a null value in the
DEPT_NUM column of the DEPT table:
SELECT E.LAST_NAME, E.FIRST_NAME, E.DEPT_NUM,
D.DEPT_NUM, D.DEPT_NAME
FROM EMPLOYEE E LEFT JOIN DEPT D
ON E.DEPT_NUM = D.DEPT_NUM
WHERE D.DEPT_NUM IS NULL
The query returns this data:
The IS NULL predicate is applied to the DEPT_NUM column of the DEPT table
because it appears in the join predicate and belongs to the table that is not
preserved.
A null value marker (?) in the column indicates that for a given department number
in the EMPLOYEE table, there is no matching department number in the DEPT
table. You can refine the report by eliminating columns selected from the DEPT
table:
SELECT E.LAST_NAME, E.FIRST_NAME, E.DEPT_NUM
FROM EMPLOYEE E LEFT JOIN DEPT D
ON E.DEPT_NUM = D.DEPT_NUM
WHERE D.DEPT_NUM IS NULL
The query returns this data:
Murakami Kazuo 6410 6410 Marketing - Korea
Nakagawa Etsuro 6400 6400 Marketing - Far East
Nakamura Eichiro 6480 6480 Marketing - Australia
Simpson Travis 7600 7600 Marketing - USA
Smith James 7620 7620 Marketing - USA West
Smythe Roger 7690 ? ?
Smith John 7690 ? ?
Smythe Roger 7690 ? ?
Smith John 7690 ? ?
Smythe Roger 7690
Smith John 7690