SQL Topics
Subquery Introduction
title: Subquery Introduction
As databases grow larger and more complex, simple SQL queries are often not enough to answer business questions.
For example:
- Find employees earning more than the average salary.
- Find customers who placed the most orders.
- Find products that cost more than the average product price.
- Find students who scored higher than the class average.
These types of problems require SQL to perform one query first and then use its result inside another query.
To solve such problems, SQL provides Subqueries.
A Subquery is a query written inside another SQL query. The result of the inner query is used by the outer query to perform additional operations.
Because of their flexibility and power, subqueries are among the most important concepts in SQL.
What is a Subquery?
A Subquery is a SQL query nested inside another query.
It is also known as:
Nested Query
Inner Query
Inner SelectThe outer query uses the result returned by the subquery.
Basic structure:
SELECT column_name
FROM table_name
WHERE column_name =
(
SELECT value
FROM another_table
);The query inside the parentheses is the subquery.
Why are Subqueries Important?
Imagine an Employees table:
| EmployeeID | EmployeeName | Salary |
|---|---|---|
| 1 | Rahul | 50000 |
| 2 | Priya | 60000 |
| 3 | Amit | 70000 |
| 4 | Neha | 80000 |
Management asks:
Find Employees
Who Earn More Than
The Average SalaryThis requires:
Step 1
Calculate average salary.
Step 2
Compare every employee salary with that average.
A Subquery performs both tasks in a single SQL statement.
Basic Subquery Example
Query:
SELECT EmployeeName
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);Subquery:
SELECT AVG(Salary)
FROM Employees;Result:
65000Outer Query:
SELECT EmployeeName
FROM Employees
WHERE Salary > 65000;Final Result:
| EmployeeName |
|---|
| ------------- |
| Amit |
| Neha |
How a Subquery Works
Example:
SELECT ProductName
FROM Products
WHERE Price >
(
SELECT AVG(Price)
FROM Products
);Execution:
The subquery executes before the outer query.
Visual Representation
Outer Query
│
▼
Subquery Executes
│
▼
Returns Result
│
▼
Outer Query Uses Result
│
▼
Final OutputSubquery Syntax
General syntax:
SELECT columns
FROM table_name
WHERE condition
(
SELECT columns
FROM table_name
);The subquery is enclosed within:
()
ParenthesesCreating a Sample Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);Insert data:
INSERT INTO Employees VALUES
(1, 'Rahul', 50000),
(2, 'Priya', 60000),
(3, 'Amit', 70000),
(4, 'Neha', 80000);Subquery Returning a Single Value
Example:
SELECT EmployeeName
FROM Employees
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
);Result:
| EmployeeName |
|---|
| ------------- |
| Amit |
| Neha |
The subquery returns:
One Valuewhich is used by the outer query.
Subquery with MAX()
Find employees earning the highest salary.
SELECT EmployeeName
FROM Employees
WHERE Salary =
(
SELECT MAX(Salary)
FROM Employees
);Result:
| EmployeeName |
|---|
| ------------- |
| Neha |
Subquery with MIN()
Find employees earning the lowest salary.
SELECT EmployeeName
FROM Employees
WHERE Salary =
(
SELECT MIN(Salary)
FROM Employees
);Result:
| EmployeeName |
|---|
| ------------- |
| Rahul |
Subquery in WHERE Clause
The most common usage.
Example:
SELECT ProductName
FROM Products
WHERE CategoryID =
(
SELECT CategoryID
FROM Categories
WHERE CategoryName = 'Electronics'
);The subquery finds the category ID.
The outer query finds products in that category.
Subquery in FROM Clause
A subquery can act like a temporary table.
Example:
SELECT *
FROM
(
SELECT EmployeeName,
Salary
FROM Employees
) AS EmployeeData;Result:
Temporary Result Setused by the outer query.
Subquery in SELECT Clause
Example:
SELECT
EmployeeName,
(
SELECT AVG(Salary)
FROM Employees
) AS AverageSalary
FROM Employees;Result:
| EmployeeName | AverageSalary |
|---|---|
| Rahul | 65000 |
| Priya | 65000 |
| Amit | 65000 |
| Neha | 65000 |
Real-World Example: E-Commerce
Tables:
ProductsRequirement:
Find Products
Priced Above AverageQuery:
SELECT ProductName
FROM Products
WHERE Price >
(
SELECT AVG(Price)
FROM Products
);Real-World Example: Banking
Requirement:
Find Accounts
With Balance Above AverageQuery:
SELECT AccountName
FROM Accounts
WHERE Balance >
(
SELECT AVG(Balance)
FROM Accounts
);Real-World Example: Education System
Requirement:
Students Scoring Above AverageQuery:
SELECT StudentName
FROM Students
WHERE Marks >
(
SELECT AVG(Marks)
FROM Students
);Types of Subqueries
Subqueries are generally classified into:
Single-Row Subqueries
Multiple-Row Subqueries
Correlated Subqueries
Nested Subqueries
Scalar SubqueriesEach type serves a different purpose.
The upcoming lessons cover these in detail.
Advantages of Subqueries
Simplifies Complex Queries
Multiple operations can be performed in one statement.
Improves Readability
Complex business logic becomes easier to understand.
Reduces Temporary Tables
Many calculations can be performed without creating extra tables.
Supports Dynamic Comparisons
Results automatically adapt to changing data.
Disadvantages of Subqueries
Can Reduce Performance
Complex nested queries may be slower on large datasets.
Difficult to Debug
Deeply nested queries can become hard to maintain.
Sometimes JOINs Are Faster
In some situations:
JOINmay perform better than a subquery.
Subquery vs JOIN
Subquery:
Query Inside QueryExample:
WHERE Salary >
(
SELECT AVG(Salary)
)JOIN:
Connects TablesExample:
INNER JOINComparison:
| Feature | Subquery | JOIN |
|---|---|---|
| Query Inside Query | Yes | No |
| Table Relationship Needed | No | Usually Yes |
| Readability | High | Moderate |
| Performance | Depends | Often Faster |
Performance Considerations
Small datasets:
Subqueries Perform WellLarge datasets:
Deeply Nested Queries
May Become ExpensivePerformance depends on:
- Indexes
- Query complexity
- Database engine
- Data volume
Common Errors
Returning Multiple Values
Wrong:
WHERE Salary =
(
SELECT Salary
FROM Employees
);If multiple salaries exist:
Errormay occur.
Missing Parentheses
Wrong:
WHERE Salary >
SELECT AVG(Salary)
FROM Employees;Always use:
()around subqueries.
Using Incorrect Data Types
Ensure the outer query and subquery return compatible values.
Excessive Nesting
Very deep nesting can reduce readability.
Best Practices
Keep Subqueries Simple
Avoid unnecessary complexity.
Use Meaningful Aliases
Especially for subqueries in the FROM clause.
Consider JOINs for Performance
Some queries perform better with JOINs.
Test Subqueries Independently
Run the inner query separately before embedding it.
Index Frequently Used Columns
Improves performance significantly.
Common Interview Questions
What is a Subquery?
A query nested inside another SQL query.
When does a Subquery execute?
Usually before the outer query.
Can a Subquery return multiple values?
Yes, depending on the operator used.
Where can a Subquery be used?
SELECT
FROM
WHERE
HAVINGclauses.
Is a Subquery always slower than a JOIN?
No.
Performance depends on the query and database engine.
Summary
Subqueries are one of SQL's most powerful features. They allow queries to use the results of other queries, making complex filtering, comparisons, and calculations possible within a single statement.
In this lesson, you learned:
- What a Subquery is
- Why Subqueries are important
- How Subqueries work
- Subquery syntax
- Subqueries in WHERE
- Subqueries in FROM
- Subqueries in SELECT
- Real-world examples
- Advantages and disadvantages
- Performance considerations
- Best practices
Mastering Subqueries is essential because they are widely used in reporting, analytics, business intelligence, and advanced SQL development.
Next Step
Continue to the next lesson:
Nested Subqueries →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Subquery Introduction.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, subqueries, subquery
Related SQL Topics