Everything About Subqueries In SQL

Wondering how subqueries in SQL can elevate your data analysis game? Curious about their impact on performance and query optimization? Ready to dive into the depths of SQL mastery?

A subquery is an intermediate-level concept in  Structured Query Language. At an intermediate level, this is the toughest & most confusing topic too. In this blog, we will try to make this topic super easy for everyone. We will proceed step by step and will give all the relevant examples to explain the concept in the best possible manner. For the practical part, we used the SQL Server platform. So, if you are using any other platform, then some of the commands might differ.

What is a Subquery in SQL?

Subqueries are queries in SQL queries embedded with WHERE, HAVING & FROM clauses. The output of the inner query becomes the input for the outer or main query. In other words, the output of the inner query is used as a condition for the outer query, filtering the result for the outer query. A Subquery is also called an inner query or nested query.

Types of Subqueries in Structured Query Language

• Single Row Subquery in SQL

A single-row subquery returns either 0 or 1 row to the outer query. Let’s create a table & understand it.

Create Table Students(

RN Varchar(10) Primary Key,

SName Varchar(20),

Marks Int,

City Varchar(15))

Insert into Students

Values(1, ‘Vineet’, 80, ‘Delhi’),

(2, ‘Pradeep’, 70, ‘Jaipur’),

(3, ‘Raman’, 90, ‘Lucknow’),

(4, ‘Dinesh’, 65, ‘Delhi’)

Select * From Students

Now, for example, you need to find out all the students who scored more than RN 2. Then you need the concept of a subquery.

Select * from Students

Where Marks> (Select Marks from Students Where RN=2)

The outcome of the inner query is a single row, which is going as input for the outer query; this is the reason this is called a Single Row Subquery.

• Multi-Row Subquery in SQL

A multi-row subquery returns more than 1 row to the outer query. For example, you need to find out the maximum number of mark holders from each city.

Select * from Students

Select * from Students

Where Marks IN (Select Max(Marks) from Students Group by City)

Here, the inner query is returning 3 rows as output, being a multi-row subquery.

• Multi Column Subquery in SQL

So far, when we were using single & multi-row subqueries in SQL, the inner query was returning a single column. If the inner query is returning more than one column, then it is called a multi-column subquery.

Multi-column subqueries help you to combine duplicate WHERE conditions into a single WHERE clause, as you can compare multiple columns with a single WHERE clause. This concept does not work in SQL Server. In the SQL server, the same case can be handled with Joins as a replacement.

• Correlated Subquery in SQL

A correlated subquery is evaluated once for each row processed by the parent statement. It is also known as a synchronised subquery or a repeating subquery. It is used whenever an inner query must return a different result or set of results for each candidate row of the outer query.

Let’s create a new table Details:

Create table Details(

Product_id varchar(255),

Product_Name varchar(255),

category varchar(255),

List_Price int)

Insert into Details

Values(‘P1’, ‘Pencil’, ‘1’, 200),

(‘P2’, ‘Pen’, ‘2’, 300),

(‘P3’, ‘Erazer’,’3′, 250),

(‘P4’, ‘Sharpener’, ‘1’, 400),

(‘P5’, ‘Notebook’,’2′, 380),

(‘P6’, ‘Pen’, ‘3’, 350)

Select * from Details

Now, we need to find the products whose list price is equal to the highest list price of the products within the same category.

Select Product_Name, List_Price from Details WHERE List_Price in (Select Max(List_Price) from Details group by Category)

This is an example of a Correlated subquery where the output of the inner query depends upon the value coming from the outer query for the comparison. So, the execution of the inner query is dependent upon the outer query. So, this query is a multi-row subquery and a correlated query.

Rules to be followed in Subquery in SQL

• The inner query is enclosed within parentheses.
• We cannot use the ORDER BY clause in the inner query, but we can use the same in the outer query.
• The BETWEEN operator cannot be used to connect the outer & inner queries, but can be used within the subquery.

FUN FACT: In the future of SQL, as our reliance on data to fuel innovation and make informed decisions grows, SQL will continue to be a crucial tool. It will adapt and evolve to meet the needs of an ever-more data-focused world.

Operators that can be used with Subquery in SQL

• Single Row Operators (=, >=, <=, >, <)

These operators are used only with single-row subqueries in Structured Query Language. Refer to the example of the single-row subquery above.

• Multi-Row Operators (IN, NOT IN, ANY, SOME, ALL, EXISTS, NOT EXISTS)

These operators are used when the inner query is returning more than 1 row. Hence, they are used in multi-row subqueries. These operators help us to compare a value with a list of values coming from the execution of the inner query. We will give the example of each operator here, one by one.

1. IN

The IN operator helps you to specify multiple values in a WHERE clause. The IN operator is a way or shorthand for multiple OR conditions.

Refer to the Multi-Row Subquery example above.

2. NOT IN

Refer to the Multi-Row Subquery example above. The same example can be used with NOT IN for the reverse results.

3. ANY

ANY is a logical operator that compares a value with a group of values returned by an inner query. It must be preceded by a comparison operator (>, >=, <, <=, =, <>) & then followed by an inner query.

The values in the column of the outer query must match one or more values (any value) in the group of values to evaluate to true.

Select Product_Name, List_Price from Details WHERE List_Price = ANY (Select Max(List_Price) from Details group by Category)

4. SOME

SOME is a logical operator that compares a value with a group of values returned by an inner query. It must be preceded by a comparison operator (>, >=, <, <=, =, <>) & then followed by an inner query.

The values in the column of the outer query must match one or more values (at least some value) in the group of values to evaluate to true.

Select Product_Name, List_Price from Details WHERE List_Price = SOME (Select Max(List_Price) from Details group by Category)

5. ALL

ALL is a logical operator that compares a value with a group of values returned by an inner query. It must be preceded by a comparison operator (>, >=, <, <=, =, <>) & then followed by an inner query.

The values in the column of the outer query must match all the values in the group of values to evaluate to true.

Select Product_Name, List_Price from Details WHERE List_Price = ALL (Select Max(List_Price) from Details group by Category)

The output is blank because all the values did not match.

6. EXISTS

The EXISTS operator tests for the presence of any record in an inner query. The EXISTS operator returns TRUE if the inner query returns one or more records.

Select * from Students

WHERE exists (Select * from Students WHERE City=’Mumbai’)

As there is no student from the city of Mumbai, the output has no records.

7. NOT EXISTS

The NOT EXISTS operator tests for the absence of any record in an inner query. The NOT EXISTS operator returns TRUE if the inner query returns zero records.

Select * from Students

WHERE not exists (Select * from Students WHERE City=’Mumbai’)

As the city of Mumbai is absent in the data, the output has all the records.

Utilize statements that incorporate Subqueries in SQL

• Select

Developers use the Select statement in Data Query Language (DQL) to query the database. In the blog, whatever tables we create, we will use a Select statement to query the database and get the output. When we use Select in the subquery concept, then it comes twice. One in the inner query & one in the outer query.

Select * from Students

Where Marks> (Select Marks from Students Where RN=2)

This query above shows the use of the Select statement in the subquery.

• Insert

Let’s understand how we can use an insert statement in a subquery. Let’s create another empty table with the same column names & data types.

Create Table Students1(

RN Varchar(10) Primary Key,

SName Varchar(20),

Marks Int,

City Varchar(15))

Insert into Students1

Select * from Students

Select * From Students1

Here, we have used Insert in the outer query & Select in the inner query. Now, this subquery has inserted all the data from table Students into Students1. You can insert values into Students1 based on certain criteria using the WHERE clause.

• Delete

Now, for example, you need to delete all the students who scored more than RN 2. Then you need the concept of a subquery. We will implement this concept in the Students1 table here.

Select * From Students1

Delete from Students1

Where Marks> (Select Marks from Students Where RN=2)

Select * From Students1

• Update

Let’s add a new column, Grade, to the table Students.

Alter table Students

add Grade Varchar(5)

The table now looks as follows:

Select * From Students

Now, we need to update this table as per the following criteria.

Students who scored more than the highest marks in Delhi deserve A+ and the rest A.

Update Students

SET Grade=’A+’

Where Marks> (Select Max(Marks) from Students Where City=’Delhi’)

Select * From Students 

Update Students

SET Grade=’A’

Where Grade is NULL

Select * From Students 

Final Words about Subqueries in SQL

We can use subqueries on a single table as well as on multiple tables. In a way, subqueries are an alternative to the Joins concept in SQL. But there are cases where subqueries in SQL are the only option, and even Joins do not help.

Subqueries are advantageous because they divide the whole statement into different queries and are more readable, but we should prefer Joins over Subqueries due to better optimization. Joins reduce the processing burden on the database by replacing multiple queries with one query.

That was all about Subqueries in  Structured Query Language. Do you want to learn more about Joins Vs Subqueries in SQL?

FAQ: Subqueries in SQL

1. Why do we even need subqueries in SQL when we already have joins?

Subqueries in SQL let you write logic step by step, making complex conditions easier to express. While joins combine tables directly, subqueries work best when one query’s result needs to filter or influence another.

2. Do subqueries always make a query slower?

Not necessarily. Small or well-indexed datasets handle subqueries efficiently. But when you nest too many or use correlated subqueries, they can slow performance since they execute multiple times.

3. How do I know if I should use a subquery or rewrite it as a join?

If you need data from multiple tables in one flat view, use a join. If you want to use the result of one query to filter another, like “students scoring above average”, a subquery makes more sense.

4. Can subqueries in SQL be used inside an UPDATE or DELETE statement?

Yes, absolutely. You can use subqueries with UPDATE, DELETE, and INSERT statements to conditionally modify or insert records. They’re perfect when your logic depends on data from another table.

5. What’s the biggest mistake beginners make with subqueries?

Using them everywhere. Many new learners wrap every condition inside a subquery instead of simplifying it with joins or temporary tables. The trick is to use subqueries only when truly needed. We also have a detailed article on Interview preparation for SQL for all levels, starting from Beginner. 

6. How can I check if my subquery is slowing down my query performance?

Most SQL tools let you view the execution plan. If you see the subquery being executed repeatedly for each row, that’s a red flag. You can usually fix it by rewriting it as a join or adding proper indexes.

7. What are correlated subqueries, and when should I avoid them?

Correlated subqueries depend on the outer query for each row, so they re-run again and again. They’re powerful but should be avoided for large datasets unless optimized carefully.

8. Can subqueries be nested more than once?

Yes, SQL allows multi-level nesting, a subquery inside another subquery. But each extra layer adds complexity. Always balance clarity and performance when nesting.

9. Why do some SQL servers not support multi-column subqueries?

It’s because certain SQL engines, like SQL Server, handle comparisons differently. You can still achieve the same result using joins or by breaking the logic into smaller parts.

10. Where can I practice subqueries in SQL with real datasets and interview-level problems?

You can join Edu4Sure’s SQL Training Program, where trainers walk you through practical examples of subqueries, performance tuning, and real interview-style exercises. It’s perfect for mastering SQL step by step. You can enrol for the Basic Level of SQL first before moving to the Advanced Level of SQL course.

Do you need professional training in SQL? You can contact us via partner@edu4sure.com or WhatsApp at +91-9555115533.