SQL allows the nesting of one query inside another, but only in the WHERE and the HAVING clauses. In addition, SQL permits a subquery only on the right hand side of an operator.
Example 1
Find the names and IDs of all faculty members who teach a class in room ‘H221’. You could do this with 2 queries as follows:
SELECT FACID FROM CLASS WHERE ROOM = ‘H221’;
—> RESULT: F101, F102
SELECT FACNAME, FACID FROM FACULTY WHERE FACID IN (F101, F102);
or you could combine the 2 into a nested query:
SELECT FACNAME, FACID FROM FACULTY WHERE FACID IN (SELECT FACID FROM CLASS WHERE ROOM = ‘H221’);
Note that the nested SELECT is executed first and its results are used as the argument to the outer SELECTs IN clause.
| FACNAME | FACID |
| Adams | F101 |
| Smith | F202 |
Example 2
Retrieve an alphabetical list of last names and IDs of all students in any class taught by faculty number ‘F110’.
SELECT LNAME, STUID FROM STUDENT WHERE STUID IN (SELECT STUID FROM ENROLL WHERE COURSENUM IN (SELECT COURSENUM FROM CLASS WHERE FACID = ‘F110’)) ORDER BY LNAME;
| LNAME | STUID |
| Burns | S1010 |
| Chin | S1002 |
| Rivera | S1020 |
The most deeply nested SELECT is done first. Thus, after the first select you have:
SELECT LNAME, STUID FROM STUDENT WHERE STUID IN (SELECT STUID FROM ENROLL WHERE COURSENUM IN (‘MTH101B’,’MTH103C’)) ORDER BY LNAME;
Next, the next most deeply is done.
SELECT LNAME, STUID FROM STUDENT WHERE STUID IN (‘S1020′,’S1010′,’S1002’) ORDER BY LNAME;
Finally, the outer Select is executed giving the result printed above.
Example 3
Find the name and IDs of students who have less than the average number of credits.
SELECT LNAME, STUID FROM STUDENT WHERE CREDITS < (SELECT AVG(CREDITS) FROM STUDENT);
| LNAME | STUID |
| Chin | S1002 |
| Rivera | S1020 |
| McCarthy | S1013 |