SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
J-2
Examples - Joins

Examples of inner joins
In these examples, rows that do not have the same department number are not
returned in the result. So, rows with employee number 5361 and 9069 do not
appear in the result because the corresponding department number value does not
exist in the DEPT table.
This query uses an inner join of the tables EMPLOYEE and DEPT:
SELECT E.LAST_NAME, E.FIRST_NAME, E.DEPT_NUM,
D.DEPT_NUM, D.DEPT_NAME
FROM EMPLOYEE E, DEPT D
WHERE E.DEPT_NUM = D.DEPT_NUM
This example shows an equivalent query that uses an INNER JOIN operator:
SELECT E.LAST_NAME, E.FIRST_NAME, E.DEPT_NUM,
D.DEPT_NUM, D.DEPT_NAME
FROM EMPLOYEE E INNER JOIN DEPT D
ON E.DEPT_NUM = D.DEPT_NUM
Both queries return this result:

Examples of left outer joins
This 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
DEPT_NUM DEPT_NAME DEPT_LOC
6400 Marketing - Far East 900
6410 Marketing - Korea 910
6420 Marketing - Hong Kong 920
6440 Marketing - Singapore 940
6470 Marketing - Taiwan 970
6480 Marketing - Australia 980
7600 Marketing - USA 100
7620 Marketing - USA West 120
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