- A subquery can be nested inside other subqueries.
- SQL has an ability to nest queries within one another.
- A subquery is a SELECT statement that is nested within another SELECT statement and which return intermediate results.
- SQL executes innermost subquery first, then next level.
- The innermost subquery executes first
- Use aliases carefully when nesting deeply
IN,ANY,ALL,=,>are common in outer queries- Always check data type and row return counts to avoid errors
SELECT ord_num,ord_amount,advance_amount FROM orders WHERE ord_amount>2000
AND ADVANCE_AMOUNT <
ANY
(
SELECT OUTSTANDING_AMT FROM CUSTOMER WHERE GRADE=3 AND CUST_COUNTRY<>'India'
AND opening_amt<7000
AND EXISTS
(
SELECT * FROM agents WHERE commission<.12
)
);
Explanation:
The last Inner query will fetched the rows
from agents table who have commission is less than .12%.
The 2nd last inner query returns the
outstanding amount for those customers who are in grade 3 and not belongs to
the country India and their deposited opening amount is less than 7000 and
their agents should have earned a commission is less than .12%.
The outer query returns ord_num, ord_date,
ord_amount, advance_amount for those orders from orders table which ord_amount
is more than 2000 and ord_date before the '01-sep-08' and the advance amount
may be the outstanding amount for those customers who are in grade 3 and not
belongs to the country India and there deposited opening amount is less than
7000 and their agents should have earned a commission is less than .12%.
