DB2 - SQL Subquery

SQL Subqueries are used to combine different queries into one single statement.

Subqueries are always processed starting with the inner most SELECT statement and working outward.

Example:

SELECT order_cust_id
    FROM tb_order
    WHERE order_num IN
      (SELECT order_item_num
         FROM tb_order_item
      WHERE order_item prod_id = 'RGAN01');

When the preceding SELECT statement is processed, the DBMS actually performs two operations.

First it runs the subquery:

SELECT order_item_num
    FROM tb_order_item
   WHERE order_item_prod_id = 'RGAN01'

This query is called “Inner Query”.

This query returns two order numbers 20007 and 20008. Those two values are then passed to the WHERE clause of the “outer query” in the comma-delimited format required by the IN operator. The outer query now becomes the following:

SELECT order_cust_id
    FROM tb_order
    WHERE order_num IN (20007,20008)

You can use subquery in a Simple Comparison:

• IN
• ANY
• SOME
• ALL
• EXIST
• If a subquery returns a single row, then the =, <, >, <=, >=, or <> operator may be used for comparison with the subquery. If multiple records are returned, the IN, ANY, ALL or SOME operator must be used. With ANY or SOME, the condition must be true for any one of the values returned by the subquery. With ALL, the condition must be true for all of the values returned by the subquery.

Example:

Subquery which uses ANY operator is as follows:

SELECT cust_no
  FROM tb_cust
  WHERE cust_no = ANY
    (SELECT inv_cust_no
       FROM tb_inv
       WHERE inv_total > 200);

Types of subqueries

1. Non-correlated subquery

2. Correlated subquery

Non-correlated subquery:

We will see Correlated subquery in next chapter

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!