Introduction to NonStop SQL/MP

Joining Tables
How to Use NonStop SQL
2–4 113425 Tandem Computers Incorporated
Joining Tables When a query selects data from two or more tables, the SELECT statement effectively
joins the tables to form a single, combined result table. For example, to list the
managers of each department and the department name, you need information from
the EMPLOYEE and DEPT tables.
Figure 2-3 illustrates a SELECT statement that joins these two tables using the
employee number, a value common to both tables. (The MANAGER column in the
DEPT table lists the employee number of each department manager.)
Figure 2-3. Selecting Data From Two Tables
SELECT FIRST_NAME, LAST_NAME, DEPTNAME
FROM EMPLOYEE, DEPT
WHERE EMPLOYEE.EMPNUM = DEPT.MANAGER
ORDER BY DEPT.DEPTNUM ;
006
EMPNUM FIRST_NAME LAST_NAME DEPTNUM JOBCODE ...
EMPLOYEE Table
FIRST_NAME LAST_NAME DEPTNAME
JERRY
•••
ROGER
HOWARD
•••
GREEN
FINANCE
•••
JANE RAYMOND
MARKETING
CORPORATE
MARY MILLER
SHIPPING
1
23
29
•••
234
•••
111
ROGER
JERRY
JANE
•••
MARY
•••
SHERRIE
GREEN
HOWARD
RAYMOND
•••
MILLER
•••
9000
1000
3000
•••
2500
•••
3500
100
100
100
•••
100
•••
100
MANAGER ...
DEPTNUM DEPTNAME
PERSONNEL
SHIPPING
CORPORATE
MARKETING
DEPT Table
FINANCE
3000
1000
1500
2500
9000
23
213
234
29
ASIA SALES3500 111
1
•••
•••
•••
WONG
SHERRIE WONG ASIA SALES
•••••••••
•••
•••
•••••••••
Select Columns
Tables to Be Joined
Select Rows in Which
the Employee Is a
Manager