Subqueries in SELECT statements

 

Subqueries further refine your search conditions in SELECT statements.

In simple WHERE and HAVING clauses, you can specify a search condition by using a literal value, a column name, an expression, or a special register. In those search conditions, you know that you are searching for a specific value. However, sometimes you cannot supply that value until you have retrieved other data from a table. For example, suppose you want a list of the employee numbers, names, and job codes of all employees working on a particular project, say project number MA2100. The first part of the statement is easy to write:

     SELECT EMPNO, LASTNAME, JOB      FROM CORPDATA.EMPLOYEE      WHERE EMPNO …

But you cannot go further because the CORPDATA.EMPLOYEE table does not include project number data. You do not know which employees are working on project MA2100 without issuing another SELECT statement against the CORPDATA.EMP_ACT table.

With SQL, you can nest one SELECT statement within another to solve this problem. The inner SELECT statement is called a subquery. The SELECT statement surrounding the subquery is called the outer-level SELECT. Using a subquery, you can issue just one SQL statement to retrieve the employee numbers, names, and job codes for employees who work on the project MA2100:

     SELECT EMPNO, LASTNAME, JOB      FROM CORPDATA.EMPLOYEE      WHERE EMPNO IN
        (SELECT EMPNO            FROM CORPDATA.EMPPROJACT            WHERE PROJNO = 'MA2100')

To better understand what will result from this SQL statement, imagine that SQL goes through the following process:

Step 1: SQL evaluates the subquery to obtain a list of EMPNO values:

(SELECT EMPNO      FROM CORPDATA.EMPPROJACT      WHERE PROJNO= 'MA2100')

The result in an interim result table follows.

EMPNO from CORPDATA.EMPPROJACT
000010
000110

Step 2: The interim result table then serves as a list in the search condition of the outer-level SELECT statement. Essentially, this is the statement that is run:

SELECT EMPNO, LASTNAME, JOB      FROM CORPDATA.EMPLOYEE      WHERE EMPNO IN 
            ('000010', '000110')

The final result table looks like this.

EMPNO	LASTNAME	JOB
000010	HAAS	PRES
000110	LUCCHESSI	SALESREP

• Subqueries and search conditions
  A subquery can be part of a search condition. The search condition is in the form of operand operator operand. Either operand can be a subquery.

• Usage notes on subqueries
  Here are some considerations for using subqueries to refine your search conditions.

• Including subqueries in the WHERE or HAVING clause
  You can include a subquery in a WHERE or HAVING clause by using a basic or quantified comparison, the IN keyword, or the EXISTS keyword.

 

Parent topic:

Using subqueries