Nested Query in SQL 

When working with databases, you may often need results based on other data. For such cases, SQL provides nested queries. These are queries placed inside another query to help filter, compare, or analyze data more effectively. Nested queries are useful when one SQL statement alone cannot produce the needed result. In this blog, we’ll explore what nested queries are, why they’re useful, their types, and how to write them using a single dataset example.

Table of Contents

• What is a Nested Query in SQL?
• Benefits of Nested Query in SQL
• Types of Nested Queries in SQL
  1. Independent (Non-Correlated) Nested Queries
  2. Correlated Nested Queries
• How to Write Nested Queries in SQL?
• Common SQL Operators Used in Nested Queries
• Common Challenges with Nested Queries in SQL
• Best Practices for Using Nested Queries in SQL
• Conclusion

What is a Nested Query in SQL?

A nested query in SQL is a query written inside another query. The inner query runs first, and its result is used by the outer query. It works like solving a small part of a problem before solving the full one. Nested queries make complex problems easier by breaking them into smaller steps. They are useful when you need to use values from another table or from a different result set.

Why Use Nested Queries in SQL?

Nested queries are helpful in situations like these:

• When you want to filter data based on some outcome from a different query. 
• When you want to compare values between tables or rows. 
• When you want to simplify complex logic without managing joins or multiple queries. 

Benefits of Nested Query in SQL

• Take a complex problem and divide it into smaller parts.
• Easy to read and understand.
• Useful when dealing with multiple sets of data.
• Helps in keeping the main query neat.
• Minimizes the amount of repeated code and logic.

Note: For understanding how nested queries work, we will use the following two tables

Let us create a student and course table and insert data into it.

---Creating the table
CREATE TABLE COURSE (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(50)
);
CREATE TABLE STUDENT (
  student_id INT PRIMARY KEY,
  name VARCHAR(50),
  course_id INT,
  FOREIGN KEY (course_id) REFERENCES COURSE(course_id)
);
-- Insert data into the COURSE table
INSERT INTO COURSE (course_id, course_name) VALUES
(101, 'Mathematics'),
(102, 'Science'),
(103, 'History');
-- Insert data into the STUDENT table
INSERT INTO STUDENT (student_id, name, course_id) VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', 101),
(4, 'David', 103);
---Displaying the result
SELECT * FROM STUDENT;
SELECT * FROM COURSE;

Output:

This is how the table looks after creating and inserting the data into it.

Become a Certified SQL Expert – Enroll Now!

Get hands-on training, real-time projects, and industry-recognized certification.

Explore Program

Types of Nested Queries in SQL

There are two types of nested queries in SQL:

1. Independent (Non-Correlated) Nested Queries

These queries do not rely on the outer query. The inner query runs first entirely on its own, and the result is passed to the outer query.

Example: Find the names of the students who are enrolled in mathematics.

SELECT name FROM STUDENT
WHERE course_id = (
  SELECT course_id FROM COURSE WHERE course_name = 'Mathematics'
);

Output:

Explanation: Here, the inner query gets the course_id for Mathematics, and the outer query finds all the students whose course_id matches that result.

2. Correlated Nested Queries

These queries rely on the outer query. The inner query runs for each row of the outer query. This means that the inner query references the outer query’s data as it runs.

Example: Find the names of students enrolled in courses that have only one student.

SELECT name FROM STUDENT S1
WHERE 1 = (
  SELECT COUNT(*) FROM STUDENT S2
  WHERE S1.course_id = S2.course_id

);

Output:

Explanation: Here, the query returns the names of students who are the only ones enrolled in their course.

How to Write Nested Queries in SQL?

Nested queries are written inside parentheses and can be placed in the WHERE, FROM, or SELECT statements of SQL. When a nested query is executed, the inner query will execute first, and its results will be passed to the outer query.

Example: Find the names of students who are enrolled in science.

SELECT name FROM STUDENT
WHERE course_id = (
  SELECT course_id FROM COURSE
  WHERE course_name = 'Science'
);

Output:

Explanation: Here, the query helps to fetch the student names by finding the course ID for science.

Common SQL Operators Used in Nested Queries

There are some operators in SQL with which we can use our nested query.

1. Using the IN Operator with Nested Query in SQL

The IN operator in SQL checks if a value exists in a list returned by the nested query or not.

Example: Find out the names of students who are in either mathematics or science.

SELECT name FROM STUDENT
WHERE course_id IN (
  SELECT course_id FROM COURSE
  WHERE course_name IN ('Mathematics', 'Science')
);

Output:

Explanation: Here, this query helps in returning the names of those students who are enrolled in either mathematics or science.

2. Using the NOT IN Operator with Nested Query in SQL

The NOT IN operator in SQL helps to return the data that does not match any value from the inner query.

Example: Find the names of those students who are not enrolled in the history.

SELECT name FROM STUDENT
WHERE course_id NOT IN (
  SELECT course_id FROM COURSE
  WHERE course_name = 'History');

Output:

Explanation: Here, this query helps to find out the names of those students who are not enrolled in the history.

3. Using the EXISTS Operator in Nested Query in SQL

The EXISTS operator in SQL checks if the inner query returns any rows or not. If it does, the outer query proceeds and gives a result.

Example: Find all the students’ names only if mathematics exists in the Course table.

SELECT name FROM STUDENT
WHERE EXISTS (
  SELECT * FROM COURSE
  WHERE course_name = 'Mathematics'
);

Output:

Explanation: Here, the query helps to list all students only if mathematics is available in the course list.

4. Using the ANY Operator in Nested Query in SQL

The ANY operator in SQL helps to compare the value to any one of the values which is returned by the inner query.

Example: Find the name of those students whose course ID is greater than any course ID returned from ‘Mathematics’ or ‘History’.

SELECT name FROM STUDENT
WHERE course_id > ANY (
  SELECT course_id FROM COURSE
  WHERE course_name IN ('Mathematics', 'History')
);

Output:

Explanation: Here, this query helps to find out the names of those students whose course ID is greater than at least one of the listed courses.

5. Using the ALL Operator in Nested Query in SQL

The ALL operator in SQL helps to compare one value to all values which is returned by a nested query.

Example: Find students whose course ID is greater than all course IDs from the COURSE table.

SELECT name FROM STUDENT
WHERE course_id > ALL (
  SELECT course_id FROM COURSE
);

Output:

Explanation: Here, this query finds out students whose course ID is greater than every course ID in the COURSE table.

Common Challenges with Nested Queries in SQL

While nested queries have their advantages, they do pose several problems:

• Performance Issues: Using subqueries, especially correlated ones, can slow down performance. You can improve speed by using JOINs or CTEs and applying the right indexes.
• Troubleshooting Difficulty: Complex nested logic is hard to debug; simplify by breaking the query into smaller CTEs or temporary views.
• Readability Concerns: Deep nesting makes queries hard to read; improve clarity by structuring queries with well-named CTEs.
• Scalability Limitations: Subqueries may not perform well on large datasets; optimize with execution plans, indexing, and query refactoring.

Best Practices for Using Nested Queries in SQL

• Keep It Simple: Write straightforward queries that are easy to read and maintain.
• Use Aliases: Apply meaningful aliases to tables and columns to make queries clearer.
• Avoid Deep Nesting: Limit nested subqueries unless needed, as they reduce readability and performance.
• Prefer EXISTS Over IN: In some databases, EXISTS it can perform better than IN with large datasets.
• Test with Real Data: Always evaluate query performance on actual data to catch real-world issues.
• Index Key Columns: Ensure indexes exist on columns used in WHERE, JOIN, and ORDER BY clauses for faster execution.

Start Learning SQL for Free!

Master SQL fundamentals at your own pace with our beginner-friendly course.

Explore Program

Conclusion

Nested queries in SQL help you solve complex problems by breaking them into smaller parts and handling each step clearly. They are especially useful when you need to filter data from multiple tables or compare different sets of values. By following best practices and writing clean, well-structured queries, you can make your SQL easier to read, more powerful, and better suited for handling a wide range of data tasks in real projects.

Take your skills to the next level by enrolling in the SQL Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions prepared by industry experts.

Nested Query in SQL – FAQs