Example: Correlated subquery in a select-list

 

Suppose that you want a list of all the departments, including the department name, number, and manager's name.

Department names and numbers are found in the CORPDATA.DEPARTMENT table. However, DEPARTMENT has only the manager's number, not the manager's name. To find the name of the manager for each department, find the employee number from the EMPLOYEE table that matches the manager number in the DEPARTMENT table and return the name for the row that matches. Only departments that currently have managers assigned are to be returned. Execute the following SQL statement:

SELECT DEPTNO, DEPTNAME, 
    (SELECT FIRSTNME CONCAT ' ' CONCAT 
         MIDINIT CONCAT ' ' CONCAT LASTNAME        FROM EMPLOYEE X        WHERE X.EMPNO = Y.MGRNO) AS MANAGER_NAME    FROM DEPARTMENT Y    WHERE MGRNO IS NOT NULL

For each row returned for DEPTNO and DEPTNAME, the system finds where EMPNO = MGRNO and returns the manager's name. The result table produced by the query has the following values.

Table 1. Result set for previous query

DEPTNO	DEPTNAME	MANAGER_NAME
A00	SPIFFY COMPUTER SERVICE DIV.	CHRISTINE I HAAS
B01	PLANNING	MICHAEL L THOMPSON
C01	INFORMATION CENTER	SALLY A KWAN
D11	MANUFACTURING SYSTEMS	IRVING F STERN
D21	ADMINISTRATION SYSTEMS	EVA D PULASKI
E01	SUPPORT SERVICES	JOHN B GEYER
E11	OPERATIONS	EILEEN W HENDERSON
E21	SOFTWARE SUPPORT	THEODORE Q SPENSER

 

Parent topic:

Correlated subqueries