Subqueries

As SQL queries become more complex, we often need the result of one query to be used inside another query.

This is where subqueries come in. A subquery is simply a query inside another query.

What is a Subquery?

A subquery is a SELECT statement nested inside another SQL statement.

The outer query uses the result of the inner query to produce the final output.

Think of it as:

• First solve the inner query
• Then use its result in the outer query

Basic Subquery Syntax

SELECT column
FROM table
WHERE column = (
    SELECT column
    FROM table
);
  

Subqueries are always written inside parentheses.

Example Table

We will continue using the employees table:

+----+----------+------------+--------+
| id | name     | department | salary |
+----+----------+------------+--------+
| 1  | Alice    | IT         | 75000  |
| 2  | Bob      | IT         | 72000  |
| 3  | Charlie  | HR         | 60000  |
| 4  | Diana    | Finance    | 70000  |
| 5  | Evan     | HR         | 58000  |
+----+----------+------------+--------+
  

Subquery in WHERE Clause

Example: Find employees who earn more than the average salary.

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

Explanation:

• The inner query calculates average salary
• The outer query compares each employee’s salary

Subquery with IN

Subqueries are often used with IN.

Example: Employees working in departments that have more than one employee.

SELECT name
FROM employees
WHERE department IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 1
);
  

The subquery returns a list of departments, which the outer query uses for filtering.

Subquery with NOT IN

Example: Employees who are the only person in their department.

SELECT name
FROM employees
WHERE department NOT IN (
    SELECT department
    FROM employees
    GROUP BY department
    HAVING COUNT(*) > 1
);
  

Subquery in SELECT Clause

Subqueries can also appear in the SELECT clause.

Example: Show each employee’s salary and the overall average salary.

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

The subquery runs once and its value is shown for each row.

Subquery in FROM Clause

A subquery in the FROM clause acts like a temporary table.

Example: Average salary per department, then filter high-paying departments.

SELECT department, avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE avg_salary > 65000;
  

Single-Row vs Multi-Row Subqueries

Common Beginner Mistakes

• Using = instead of IN for multi-row subqueries
• Forgetting parentheses around subqueries
• Writing inefficient nested queries
• Not understanding execution order

Performance Note

While subqueries are powerful, they are sometimes slower than JOINs.

In many cases, a JOIN can replace a subquery with better performance.

We will compare JOINs vs subqueries in later lessons.

Why Subqueries Matter

Subqueries allow you to:

• Break complex problems into steps
• Write expressive and readable queries
• Reuse query results logically

What’s Next?

In the next lesson, we will explore Views, which allow you to save queries as virtual tables.