NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
J-2
Examples - Joins
•
Examples of inner joins
In the following 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.
For example, 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
The following 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
4096 Murakami Kazuo 6410 3598 36000.00
5361 Smythe Roger 7690 9069 42650.00
9069 Smith John 7690 2705 38760.00
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
EMPLOYEE TABLE
EMP_ID LAST_NAME FIRST_NAME DEPT_NUM MGR_ID SALARY