Nested Query in SQL (With Examples)

If you have been writing queries in SQL for a while, and you have faced certain situations where you need one query to use the result from another query, “Is it possible to use a Nested query in SQL?” is a question you could have. Yes, it’s possible! In this tutorial, we will learn about the Nested query with multiple SELECT statements nested together.

Understanding Nested SELECT in SQL

In SQL, a Nested SELECT query is a way to perform complex queries by nesting a query inside another. It is a query that is included inside another query and is used to apply criteria based on the output of another query or fetch data from multiple tables. The outer query is executed using the outcome of the inner query.

Example:

SELECT column01, column02, ...
FROM table01
WHERE condition IN (SELECT column01 FROM table02 WHERE condition);

We are going to use these two tables, i.e. Students and Teachers, in this tutorial.

Now, if we want to fetch the record of all the students whose Teacher name is ‘Manan’, then:

SELECT id, name
FROM Students
WHERE class_id IN (
    SELECT class_id
    FROM Teachers
    WHERE name = 'Manan'
);Code language: JavaScript (javascript)

In the above query,

• The inner SELECT statement fetches the class_id from the Teachers table where the teacher’s name is ‘Manan’.
• The outer SELECT statement fetches the id and name from the Students table where the class_id matches any values returned by the inner SELECT statement.

Also Read: MySQL Subqueries

Types of Nested Queries in SQL

In SQL, nested SELECT statements can be categorised into two main types:

• Independent Subquery
• Correlated Subquery

Independent Subquery

The innermost query is executed first in independent nested queries and then the outermost query. Although the inner query’s execution occurs independently of the outer query, the outer query uses the inner query’s result. Various operators are used when creating independent nested queries, such as IN, NOT IN, ANY, ALL, etc.

Example:

SELECT column01
FROM table01
WHERE column02 IN (SELECT column02 FROM table2);Code language: SQL (Structured Query Language) (sql)

Now, if we want to fetch the record of all the students whose Teacher name is ‘Priya’, then:

FROM Students
WHERE class_id IN (
    SELECT class_id
    FROM Teachers
    WHERE name = 'Priya'
);Code language: JavaScript (javascript)

• The inner SELECT statement fetches the class_id from the Teachers table where the teacher’s name is ‘Priya’.
• The outer SELECT statement fetches the id and name from the Students table where the class_id matches any values returned by the inner SELECT statement.

Correlated Subquery

Correlated subqueries are dependent on the outer query. The inner query is executed for each row executed by the outer query. They are often used when the result of the inner query depends on the values of the outer query. It runs slowly because each row of the output of the outer query is processed by the inner query.

Example:

SELECT column01
FROM table01 t1
WHERE t1.column02 IN (
    SELECT column02
    FROM table2 t2
    WHERE t2.column03 = t1.column04
);

Now, if we want to select students whose class_id exists in the set of class_id values from the Teachers table,

SELECT id, name
FROM Students s
WHERE s.class_id IN (
    SELECT class_id
    FROM Teachers t
WHERE t.class_id=s.class_id
);

• The outer SELECT statement fetches the id and name from the Students table.
• The inner subquery selects class_id from the Teachers table where class_id matches the class_id of the current row in the outer query.
• The outer query filters the rows from Students where the class_id is found in the set of class_id values which is returned by the inner subquery.

Conclusion

In this tutorial, we have learnt about the Nested SELECT with multiple examples of relational database tables, also we seen the main types of Nested Subqueries, i.e., Independent and Correlated Subqueries and got to know how and in what situation one can use them. It is one of the most important topics for preparing for Job interviews. We hope you find this article informative and enjoyable.

Reference

https://stackoverflow.com/questions/12467354/nesting-queries-in-sql