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.
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:
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
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!