4.2.1.2 Nested SELECT statements

Nested SELECT statements

SQL allows nesting of SELECT statements. In a nested SELECT statement the inner SELECT is evaluated first and is replaced by its result to evaluate the outer SELECT statement.

Query 20:

SELECT item#, descr, price <--------------------------- FROM items WHERE price > (SELECT AVG(price) FROM items); <------

Outer SELECT statement Inner SELECT statement

Query 20 - Result:

Item#	Descr	Price
HW1	Power Supply	4000
SW1	MS-DOS 6.0	5000
SW2	MS-Word 6.0	8000

Query 21:

SELECT cust#, custname <------------------

FROM customers

WHERE city = ( SELECT city FROM customers

WHERE custname='Shah');

Result :

SELECT cust#, custname <------------------

FROM customers

WHERE city = ( SELECT city FROM customers

WHERE custname='Shah');

Here the outer SELECT is evaluated as
SELECT cust#, custname
FROM customers
WHERE city = "BOMBAY"