SQL Subqueries Explained with Examples + Practice Questions

What are Subqueries

A subquery is a query written inside another query.

It is used to:

• Filter data using another query
• Compare values dynamically
• Solve complex problems step-by-step

When to Use Subqueries

• When result depends on another query
• When filtering based on aggregated values
• When solving interview problems like:
  • second highest salary
  • above average salary

Example Dataset

Table: employees

Basic Subquery

Example: Employees earning above average salary

sql
SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

Types of Subqueries

1. Single Row Subquery

Returns one value.

sql
SELECT name
FROM employees
WHERE salary = (
    SELECT MAX(salary) FROM employees
);

1. Multiple Row Subquery

Returns multiple values.

sql
SELECT name
FROM employees
WHERE department IN (
    SELECT department FROM employees WHERE salary > 50000
);

1. Subquery with EXISTS

Checks if rows exist.

sql
SELECT name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM employees
    WHERE department = e.department
    AND salary > 60000
);

Subquery in SELECT

sql
SELECT name,
    (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Subquery in FROM (Derived Table)

sql
SELECT department, avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg;

Subquery vs JOIN (Important Insight)

• Subqueries are easier to write
• JOINs are often more efficient

Interviews often accept both approaches.

Key Concepts

• Subquery runs first (inner query)
• Outer query uses its result
• Can return single or multiple values
• Used in WHERE, SELECT, FROM

Common Questions (FAQ)

What is a subquery

A query inside another SQL query used to compute intermediate results.

When should we use subqueries

When logic depends on another query result.

Are subqueries slow

Sometimes, especially nested ones. JOINs can be faster.

Can subqueries return multiple values

Yes, depending on usage (IN, EXISTS).

Internal Learning Links

• Learn Aggregations
• Learn GROUP BY
• Learn JOINs