Problem: What are DB2 subqueries and what is the use of subqueries? Explain subqueries with the help of an example on the ORDERS table.
A subquery in DB2 is a query within a query, i.e., nested query. A subquery is executed first followed by its parent query. We can have a subquery up to 15 levels.
For example, if we have below ORDERS table in DB2.
If we want to extract all the orders from the ORDERS table which have ORDER_TOTAL more than overall average, then we can use the below sub query.
SELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)
The result of the above query will be as below.