🔄 SQL Subquery Explained – Mastering Nested Queries with Real Examples

If you’re working with SQL, you’ve probably come across the term subquery—a query within another query. This tutorial helps you understand what subqueries are, how they work, and where to use them effectively in your SQL projects.

Whether you’re a data analyst, SQL developer, or student, mastering subqueries is essential to solving real-world database problems.

📘 What is a SQL Subquery?

A subquery, also called a nested query or inner query, is a query embedded within another SQL query.

It allows you to:

Use results from one query inside another
Filter or compare data dynamically
Build powerful and flexible SQL logic

🔍 Syntax:

SELECT column_name
FROM table_name
WHERE column_name = (
    SELECT column_name
    FROM another_table
    WHERE condition
);

🧠 When to Use Subqueries?

SQL subqueries are commonly used in:

WHERE clause to filter based on dynamic criteria
FROM clause to create virtual tables (derived tables)
SELECT clause to include calculated values

🛠️ SQL Subquery Examples

📌 1. Subquery in WHERE Clause

Get all employees who earn more than the average salary:

SELECT emp_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

🔎 Explanation: The subquery calculates the average salary, and the outer query returns employees earning above that.

📌 2. Subquery in FROM Clause (Derived Table)

Show department-wise highest salaries:

SELECT dept_id, MAX(salary) AS max_salary
FROM (
    SELECT dept_id, salary
    FROM employees
) AS dept_salaries
GROUP BY dept_id;

📌 Use Case: When you want to group or filter the results of a subquery like a temporary table.

📌 3. Subquery in SELECT Clause

Get employee name with their department name using subquery:

SELECT 
  emp_name,
  (SELECT dept_name 
   FROM departments 
   WHERE departments.dept_id = employees.dept_id) AS dept_name
FROM employees;

✅ Advantage: No need to use JOINs for small related lookups.

🔁 Correlated vs. Non-Correlated Subqueries

Type	Definition	Executes Per Row?
Non-Correlated	Independent subquery that runs once	No
Correlated	Depends on outer query; runs once per row in outer query	Yes

🧪 Example: Correlated Subquery

SELECT emp_name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE dept_id = e.dept_id
);

🔄 Here: The subquery uses the outer query’s dept_id—this is what makes it correlated.

🧩 SQL Subqueries with IN, ANY, ALL, EXISTS

✅ Subquery with `IN`

SELECT emp_name
FROM employees
WHERE dept_id IN (
    SELECT dept_id
    FROM departments
    WHERE location = 'Delhi'
);

🔁 Subquery with `EXISTS`

SELECT dept_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.dept_id = d.dept_id
);

📌 Subquery with `ALL`

SELECT emp_name
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE dept_id = 2
);

🚫 SQL Subquery Limitations

Some RDBMS restrict subqueries in certain clauses like LIMIT, ORDER BY
Complex subqueries may impact performance
Use JOINs when appropriate—they’re often more efficient than subqueries

💡 Best Practices

Prefer correlated subqueries only when needed
Replace repetitive subqueries with Common Table Expressions (CTEs) or derived tables
Analyze performance with EXPLAIN or query execution plans

Subquery