What is a subquery in SQL?

A Subquery in SQL is a query written inside another SQL query.

In simple words:

A subquery is an inner query whose result is used by the outer query.

Why Subqueries are Important

In enterprise applications:

• Complex data filtering is required
• Dependent queries are common
• Dynamic conditions are needed

Subqueries help:

• Break complex queries into smaller parts
• Improve query readability
• Perform nested data retrieval
• Handle advanced filtering logic

Simple Real-Life Example

Think about:

• Finding students whose marks are above average

Steps

• First calculate average marks
• Then compare all students against average

This Requires

One query inside another query.

Subquery Internal Architecture

Execute Inner Query
        |
        v
Return Intermediate Result
        |
        v
Outer Query Uses Result
        |
        v
Final Output Returned

Basic Subquery Syntax

SELECT column_name

FROM table_name

WHERE column_name operator (

    SELECT column_name
    FROM another_table

);

Simple Subquery Example

Students Table:

Requirement

Find students scoring above average marks.

Subquery Solution

SELECT name, marks

FROM students

WHERE marks > (

    SELECT AVG(marks)
    FROM students

);

What Happens Internally?

Step 1

Inner query executes:

SELECT AVG(marks)
FROM students;

Result

80

Step 2

Outer query becomes:

SELECT name, marks

FROM students

WHERE marks > 80;

Final Result

Subquery Query Flow

Run Inner Query
      |
      v
Get Intermediate Result
      |
      v
Pass Result to Outer Query
      |
      v
Return Final Output

Types of Subqueries

• Single Row Subquery
• Multiple Row Subquery
• Correlated Subquery
• Nested Subquery

1. Single Row Subquery

Returns:

• Only one row

Example

SELECT *
FROM employees

WHERE salary > (

    SELECT AVG(salary)
    FROM employees

);

2. Multiple Row Subquery

Returns:

• Multiple rows

Example

SELECT name

FROM students

WHERE course_id IN (

    SELECT course_id
    FROM courses
    WHERE duration > 6

);

Why IN Operator?

Because:

• Subquery returns multiple values

3. Correlated Subquery

A correlated subquery:

• Depends on outer query values

Example

SELECT e1.employee_name

FROM employees e1

WHERE salary > (

    SELECT AVG(salary)

    FROM employees e2

    WHERE e1.department_id = e2.department_id

);

What Happens?

Subquery executes:

• For every row of outer query

Why Correlated Subqueries are Slower

Because:

• Inner query runs repeatedly

4. Nested Subquery

Subquery inside another subquery.

Example

SELECT name

FROM students

WHERE course_id = (

    SELECT course_id

    FROM courses

    WHERE trainer_id = (

        SELECT trainer_id
        FROM trainers
        WHERE trainer_name = 'David'

    )

);

Subquery Locations in SQL

Subqueries can be used in:

• SELECT clause
• FROM clause
• WHERE clause
• HAVING clause

Subquery in SELECT Clause

SELECT name,

(

    SELECT AVG(marks)
    FROM students

) AS average_marks

FROM students;

Subquery in FROM Clause

SELECT *

FROM (

    SELECT *
    FROM employees
    WHERE salary > 50000

) AS high_salary_employees;

Subquery in HAVING Clause

SELECT department_id,
       AVG(salary)

FROM employees

GROUP BY department_id

HAVING AVG(salary) > (

    SELECT AVG(salary)
    FROM employees

);

Subquery Operators

• IN
• EXISTS
• ANY
• ALL

1. IN Operator

Checks:

• Whether value exists in subquery result

Example

WHERE course_id IN (

    SELECT course_id
    FROM courses

)

2. EXISTS Operator

Checks:

• Whether subquery returns rows

Example

SELECT name

FROM students s

WHERE EXISTS (

    SELECT 1
    FROM enrollments e
    WHERE s.student_id = e.student_id

);

3. ANY Operator

Condition true if:

• Matches any subquery value

4. ALL Operator

Condition true if:

• Matches all subquery values

Subquery vs JOIN

Why JOIN is Sometimes Better?

Because:

• Database optimizers handle joins efficiently

Real-Time Banking Example

Banking systems use subqueries for:

• High-value customer analysis
• Fraud detection
• Balance comparisons

Example

Find customers whose balance is above branch average.

Real-Time E-Commerce Example

E-commerce platforms use subqueries for:

• Top-selling products
• High-value orders
• Inventory analysis

Example

Find products priced above average category price.

Real-Time Learning Platform Example

Learning platforms use subqueries for:

• Top-performing students
• Course analytics
• Progress comparisons

Example

Find students scoring above course average.

Subqueries in Microservices

Microservices use subqueries for:

• Reporting APIs
• Analytics queries
• Dashboard calculations

Advantages of Subqueries

• Improves readability
• Supports complex filtering
• Breaks large queries into smaller parts
• Useful for dynamic calculations

Disadvantages of Subqueries

• Can reduce performance
• Correlated subqueries are expensive
• Difficult debugging in nested queries

Performance Consideration

Large subqueries may:

• Increase execution time

Best Practices

• Use JOINs when performance matters
• Avoid deeply nested subqueries
• Optimize correlated subqueries carefully
• Use indexes on filtered columns

Common Interview Mistake

Many developers think:

• Subqueries always execute after outer query

Reality

Usually:

• Inner query executes first

except:

• Correlated subqueries

Related Learning Topics

• Different Types of SQL Joins
• What is a View in SQL?
• What is an Index in SQL?
• MySQL Performance Optimization
• What is Normalization in SQL?

Professional Interview Answer

A Subquery in SQL is a query written inside another SQL query, where the result of the inner query is used by the outer query. Subqueries are commonly used for complex filtering, dynamic calculations, nested data retrieval, and conditional logic. They can return single rows, multiple rows, or correlated results depending on the use case. Subqueries can be used in SELECT, FROM, WHERE, and HAVING clauses. Although subqueries improve readability and simplify complex logic, correlated or deeply nested subqueries may affect performance, and JOIN operations are often preferred for optimization in enterprise applications.

Why Interviewers Like This Answer

• Clearly explains inner and outer query concepts
• Includes different subquery types
• Shows performance understanding
• Provides real-world enterprise examples
• Explains JOIN vs Subquery trade-offs

Frequently Asked Questions

What is a subquery in SQL?

A subquery is a query written inside another SQL query.

Why are subqueries used?

Subqueries are used for complex filtering and nested data retrieval.

What is a correlated subquery?

A correlated subquery depends on values from the outer query.

Are subqueries slower than joins?

Sometimes yes, especially correlated subqueries.

Where can subqueries be used?

In SELECT, FROM, WHERE, and HAVING clauses.