SQL Notes

Hub Advantages and Disadvantages of SQL Features of SQL History of SQL SQL vs NoSQL What is SQL?SQL Comments SQL Data Types SQL Keywords SQL Operators SQL Syntax CHECK Constraint in SQL Composite Keys in SQL DEFAULT Constraint in SQL FOREIGN KEY Constraint in SQL NOT NULL Constraint in SQL PRIMARY KEY Constraint in SQL SQL Constraints UNIQUE Key Constraint in SQL DELETE Data INSERT Data SELECT Data SQL Aliases UPDATE Data BETWEEN Operator DISTINCT Keyword GROUP BY Clause HAVING Clause IN Operator LIKE Operator LIMIT Clause ORDER BY Clause TOP Clause WHERE Clause Wildcard Characters EXCEPT Operator in SQL INTERSECT Operator in SQL MINUS Operator in SQL UNION Operator in SQL UNION ALL Operator in SQL CROSS JOIN in SQL FULL OUTER JOIN INNER JOIN JOIN vs UNION LEFT JOIN RIGHT JOIN SELF JOIN SQL Joins Overview ANY and ALL Operators CASE Expression in SQL Correlated Subquery EXISTS Operator Nested Subqueries in SQL Scalar Subqueries Subquery Introduction Aggregate Functions AVG Function Conversion Functions COUNT Function Date Functions MIN and MAX Functions Numeric Functions String Functions SUM Function ALTER TABLE Create Table DROP TABLE Rename Table Temporary Table TRUNCATE TABLE Backup Database Create Database Drop Database Restore Database CREATE VIEW in SQL DROP VIEW in SQL Materialized Views in SQL Update and Modify Views in SQL Create Stored Procedure in SQL Execute Stored Procedures in SQL Parameters in Stored Procedures Stored Procedure vs Function in SQL Stored Procedure Introduction AFTER Trigger BEFORE Trigger INSTEAD OF Trigger Trigger Examples Trigger Introduction ACID Properties in SQL COMMIT in SQL Isolation Levels in SQL ROLLBACK in SQL SAVEPOINT in SQL Introduction to SQL Transactions Boyce-Codd Normal Form (BCNF)Database Normalization Denormalization First Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Clustered Index in SQL Index Performance Non-Clustered Index Introduction to SQL Indexes Unique Index SQL Execution Plans EXPLAIN Plan Indexing Strategies Partitioning Query Optimization SQL Performance Tips Common Table Expressions (CTE)Dynamic SQL Functions in SQL Materialized Views MERGE Statement PIVOT and UNPIVOT RANK(), DENSE_RANK(), and ROW_NUMBER()Recursive CTE Stored Procedures Triggers in SQL Views in SQL Window Functions SQL Challenges SQL Exercises (Advanced)SQL Exercises (Beginner)SQL Exercises (Intermediate)SQL Projects SQL Interview Questions (Advanced)SQL Interview Questions (Beginner)SQL Interview Questions (Intermediate)SQL Output-Based Interview Questions SQL Scenario-Based Interview Questions Create Your First Database Install MySQL on Your Computer Install PostgreSQL Install SQLite SQL Online Compilers SQL Roadmap

Subquery Introduction

Learn SQL Subqueries in detail, understand how queries can be nested inside other queries, and discover real-world use cases for filtering, comparisons, and advanced data retrieval.

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.

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:

Output

Find Employees
Who Earn More Than
The Average Salary

This 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:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Subquery:

sql example

SELECT AVG(Salary)
FROM Employees;

Result:

Output

Outer Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > 65000;

Final Result:

EmployeeName
-------------
Amit
Neha

How a Subquery Works

Example:

sql example

SELECT ProductName
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Execution:

Example

Step 1: Calculate Average Price ↓ Step 2: Return Average Value ↓ Step 3: Outer Query Uses Result ↓ Step 4: Final Output Produced

The subquery executes before the outer query.

Visual Representation

text example

Outer Query
      │
      ▼
Subquery Executes
      │
      ▼
Returns Result
      │
      ▼
Outer Query Uses Result
      │
      ▼
Final Output

Subquery Syntax

General syntax:

sql example

SELECT columns
FROM table_name
WHERE condition
(
    SELECT columns
    FROM table_name
);

The subquery is enclosed within:

Output

()
Parentheses

Creating a Sample Table

sql example

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Salary DECIMAL(10,2)
);

Insert data:

sql example

INSERT INTO Employees VALUES
(1, 'Rahul', 50000),
(2, 'Priya', 60000),
(3, 'Amit', 70000),
(4, 'Neha', 80000);

Subquery Returning a Single Value

Example:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Result:

EmployeeName
-------------
Amit
Neha

The subquery returns:

Output

One Value

which is used by the outer query.

Subquery with MAX()

Find employees earning the highest salary.

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary =
(
    SELECT MAX(Salary)
    FROM Employees
);

Result:

EmployeeName
-------------
Neha

Subquery with MIN()

Find employees earning the lowest salary.

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary =
(
    SELECT MIN(Salary)
    FROM Employees
);

Result:

EmployeeName
-------------
Rahul

Subquery in WHERE Clause

The most common usage.

Example:

sql 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:

sql example

SELECT *
FROM
(
    SELECT EmployeeName,
           Salary
    FROM Employees
) AS EmployeeData;

Result:

Output

Temporary Result Set

used by the outer query.

Subquery in SELECT Clause

Example:

sql 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:

Output

Products

Requirement:

text example

Find Products
Priced Above Average

Query:

sql example

SELECT ProductName
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Real-World Example: Banking

Requirement:

Output

Find Accounts
With Balance Above Average

Query:

sql example

SELECT AccountName
FROM Accounts
WHERE Balance >
(
    SELECT AVG(Balance)
    FROM Accounts
);

Real-World Example: Education System

Requirement:

Output

Students Scoring Above Average

Query:

sql example

SELECT StudentName
FROM Students
WHERE Marks >
(
    SELECT AVG(Marks)
    FROM Students
);

Types of Subqueries

Subqueries are generally classified into:

Output

Single-Row Subqueries
Multiple-Row Subqueries
Correlated Subqueries
Nested Subqueries
Scalar Subqueries

Each 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:

text example

JOIN

may perform better than a subquery.

Subquery vs JOIN

Subquery:

text example

Query Inside Query

Example:

sql example

WHERE Salary >
(
    SELECT AVG(Salary)
)

JOIN:

Output

Connects Tables

Example:

sql example

INNER JOIN

Comparison:

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:

Output

Subqueries Perform Well

Large datasets:

text example

Deeply Nested Queries
May Become Expensive

Performance depends on:

Indexes
Query complexity
Database engine
Data volume

Common Errors

Returning Multiple Values

Wrong:

sql example

WHERE Salary =
(
    SELECT Salary
    FROM Employees
);

If multiple salaries exist:

Output

Error

may occur.

Missing Parentheses

Wrong:

sql example

WHERE Salary >
SELECT AVG(Salary)
FROM Employees;

Always use:

Output

()

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?

text example

SELECT
FROM
WHERE
HAVING

clauses.

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 Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, subquery, introduction

Continue learning this concept

PreviousScalar Subqueries NextAggregate Functions

SQL Notes

Subquery Introduction

Learn SQL Subqueries in detail, understand how queries can be nested inside other queries, and discover real-world use cases for filtering, comparisons, and advanced data retrieval.

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.

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:

Output

Find Employees
Who Earn More Than
The Average Salary

This 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:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Subquery:

sql example

SELECT AVG(Salary)
FROM Employees;

Result:

Output

Outer Query:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary > 65000;

Final Result:

EmployeeName
-------------
Amit
Neha

How a Subquery Works

Example:

sql example

SELECT ProductName
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Execution:

Example

Step 1: Calculate Average Price ↓ Step 2: Return Average Value ↓ Step 3: Outer Query Uses Result ↓ Step 4: Final Output Produced

The subquery executes before the outer query.

Visual Representation

text example

Outer Query
      │
      ▼
Subquery Executes
      │
      ▼
Returns Result
      │
      ▼
Outer Query Uses Result
      │
      ▼
Final Output

Subquery Syntax

General syntax:

sql example

SELECT columns
FROM table_name
WHERE condition
(
    SELECT columns
    FROM table_name
);

The subquery is enclosed within:

Output

()
Parentheses

Creating a Sample Table

sql example

CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    Salary DECIMAL(10,2)
);

Insert data:

sql example

INSERT INTO Employees VALUES
(1, 'Rahul', 50000),
(2, 'Priya', 60000),
(3, 'Amit', 70000),
(4, 'Neha', 80000);

Subquery Returning a Single Value

Example:

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Result:

EmployeeName
-------------
Amit
Neha

The subquery returns:

Output

One Value

which is used by the outer query.

Subquery with MAX()

Find employees earning the highest salary.

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary =
(
    SELECT MAX(Salary)
    FROM Employees
);

Result:

EmployeeName
-------------
Neha

Subquery with MIN()

Find employees earning the lowest salary.

sql example

SELECT EmployeeName
FROM Employees
WHERE Salary =
(
    SELECT MIN(Salary)
    FROM Employees
);

Result:

EmployeeName
-------------
Rahul

Subquery in WHERE Clause

The most common usage.

Example:

sql 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:

sql example

SELECT *
FROM
(
    SELECT EmployeeName,
           Salary
    FROM Employees
) AS EmployeeData;

Result:

Output

Temporary Result Set

used by the outer query.

Subquery in SELECT Clause

Example:

sql 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:

Output

Products

Requirement:

text example

Find Products
Priced Above Average

Query:

sql example

SELECT ProductName
FROM Products
WHERE Price >
(
    SELECT AVG(Price)
    FROM Products
);

Real-World Example: Banking

Requirement:

Output

Find Accounts
With Balance Above Average

Query:

sql example

SELECT AccountName
FROM Accounts
WHERE Balance >
(
    SELECT AVG(Balance)
    FROM Accounts
);

Real-World Example: Education System

Requirement:

Output

Students Scoring Above Average

Query:

sql example

SELECT StudentName
FROM Students
WHERE Marks >
(
    SELECT AVG(Marks)
    FROM Students
);

Types of Subqueries

Subqueries are generally classified into:

Output

Single-Row Subqueries
Multiple-Row Subqueries
Correlated Subqueries
Nested Subqueries
Scalar Subqueries

Each 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:

text example

JOIN

may perform better than a subquery.

Subquery vs JOIN

Subquery:

text example

Query Inside Query

Example:

sql example

WHERE Salary >
(
    SELECT AVG(Salary)
)

JOIN:

Output

Connects Tables

Example:

sql example

INNER JOIN

Comparison:

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:

Output

Subqueries Perform Well

Large datasets:

text example

Deeply Nested Queries
May Become Expensive

Performance depends on:

Indexes
Query complexity
Database engine
Data volume

Common Errors

Returning Multiple Values

Wrong:

sql example

WHERE Salary =
(
    SELECT Salary
    FROM Employees
);

If multiple salaries exist:

Output

Error

may occur.

Missing Parentheses

Wrong:

sql example

WHERE Salary >
SELECT AVG(Salary)
FROM Employees;

Always use:

Output

()

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?

text example

SELECT
FROM
WHERE
HAVING

clauses.

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 Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, subqueries, subquery, introduction

Continue learning this concept

PreviousScalar Subqueries NextAggregate Functions