Purpose and usage of subqueries in DB with the help of an example

DB2DatabaseBig Data Analytics

Problem: What are DB2 subqueries and what is the use of subqueries? Explain subqueries with the help of an example on the ORDERS table.

Solution

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.

ORDER_ID
ORDER_TOTAL
Z22345
1267
Z62998
6734
Z56902
8815
Z56911
78990
Z56915
432
Z56918
77453

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.

Example

SELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)

The result of the above query will be as below.

ORDER_ID
ORDER_TOTAL
Z22345
78990
Z62998
77453
raja
Published on 30-Nov-2020 09:27:04
Advertisements