NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
J-3
Examples - Joins
Both queries return the following result:
•
Examples of left outer joins
The following query retrieves all rows from the EMPLOYEE table. Employees with
employee numbers 5361 and 9069 are in department 7690. Because there is no
matching department number value in the DEPT table, columns selected from the
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 the following 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, as follows:
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
Murakami Kazuo 6410 6410 Marketing - Korea
Nakagawa Etsuro 6400 6400 Marketing - Far Eas
t
Nakamura Eichiro 6480 6480 Marketing - Australia
Simpson Travis 7600 7600 Marketing - USA
Smith James 7620 7620 Marketing - USA
West
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 ??