A nested SELECT is a query within a query, i.e. when a SELECT statement within the main SELECT.
SQL sub queries are a powerful tool. They allow us to perform tasks more efficiently by having only one query instead of several.
- Sub queries can return single values or tables (with one or many rows and columns).
- also include a sub query:
In the WHERE clause, to filter data.
In the FROM clause, to specify a new table.
In the SELECT clause, to specify a certain column.
In the HAVING clause, as a group selector.
- Sub queries should always be enclosed in parentheses ().
- Different database management systems have certain limitations on the number of sub query levels (e.g. up to 32 levels in SQL Server).
- Sub queries are often computationally inefficient. Thus, I recommend avoiding nested queries when other options are available (e.g. JOINs).
Students
| Id | Name | classID | Marks |
| 1 | Sagar | 3 | 2.4 |
| 2 | Shikha | 3 | 1.44 |
| 3 | Jasmeen | 1 | 3.24 |
| 4 | Deepika | 2 | 2.67 |
| 5 | Daljit | 2 | 4.56 |
Teachers
| Id | Name | Subject | classID | Salary |
| 1 | Bhanu | Computer | 3 | 5000 |
| 2 | Rekha | Science | 1 | 5000 |
| 3 | Siri | Social | NULL | 4500 |
| 4 | Kittu | Maths | 2 | 5500 |
Classes
| Id | Grade | teacherID | No.ofstudents |
| 1 | 8 | 2 | 20 |
| 2 | 9 | 3 | 40 |
| 3 | 10 | 1 | 38 |
Example Of Nested Query
SELECT * FROM student
WHERE classID = (
SELECT id
FROM class
WHERE noofstudents = (
SELECT MAX(noofstudents)
FROM class));
Output
4|Deepika |2|2.67
5|Daljit |2|4.56