SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-53
Types of Join Queries
Types of Join Queries
You can perform two types of join operations using SQL:
•
An inner join operation returns all rows that satisfy a given search condition. The
inner join is useful for reporting information that satisfies a particular set of
requirements—for example, salespersons who have booked orders.
•
A left join operation, or left outer join, returns all rows that satisfy a given search
condition plus those rows in the left table (listed left of the JOIN keyword) that fail
to satisfy the condition—for example, salespersons who have not booked any
orders. These rows, the exceptions to the rule represented by the join condition,
can provide useful information for exception reports.
Figure 1-3. Selecting From Two Tables
VST0103.vsd
005
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE
EMPLOYEE
1
23
29
213
234
32
FIRST_NAME LAST_NAME DEPTNAME
JERRY
ROBERT
THOMAS
ROGER
•••
HOWARD
WHITE
RUTLOFF
GREEN
•••
FINANCE
PERSONNEL
INVE N TORY
CORPORATE
•••
•••
ROGER
JERRY
JANE
ROBERT
MARY
THOMAS
•••
WHITE
GREEN
HOWARD
RAYMOND
MI LL E R
RUTLOFF
•••
1500
9000
1000
3000
2500
2000
•••
100
100
100
100
100
100
•••
1000
1500
2000
2500
4100
9000
INV EN TO RY
CORPORATE
23
213
32
234
87
1
•••
PERSONNEL
PLANNING
SHIPPING
FINANCE
••• •••
SELECT FIRST_NAME, LAST_NAME, DEPTNAME
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.EMPNUM = DEPT.MANAGER
ORDER BY DEPT.DEPTNUM ;
DEPT
DEPTNUM DEPTNAME MANAGER