When working with relational databases, you will frequently need to perform complex data filters that depend on other database values. To write advanced queries, developers must master subqueries in sql, which allow you to nest one query inside another to compute values dynamically.

In this guide, we will break down the different types of subqueries in sql, explain when to use them over standard JOINs, and provide clear examples you can run on any database platform.

What is a Subquery in SQL?

A subquery (also known as an inner query or nested query) is a SELECT statement nested inside another SQL statement, such as a SELECT, INSERT, UPDATE, or DELETE. The inner query runs first and returns its result set, which is then used by the outer query to filter or compute the final result.

The 4 Main Types of Subqueries in SQL

Subqueries are classified based on the type of data they return and how they interact with the outer query:

1. Scalar Subqueries

A scalar subquery returns exactly one single value (one row and one column). It is typically used with comparison operators like =, >, or < in the WHERE clause. For example, to find all employees earning above the company’s average salary:

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

2. Multi-Row Subqueries

A multi-row subquery returns a column of multiple values (multiple rows, one column). Because it returns a list, it must be paired with operators like IN, ANY, ALL, or EXISTS. For example, to find all employees working in departments located in London:

SELECT first_name, last_name 
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'London');

3. Correlated Subqueries

A correlated subquery is a nested query that references one or more columns from the outer query. Unlike standard subqueries, which run once, a correlated subquery executes repeatedly—once for each row processed by the outer query. For example, to retrieve customer details along with a count of their registered cars:

SELECT c.id, c.first_name, c.last_name,
       (SELECT COUNT(*) FROM cars WHERE cars.customer_id = c.id) AS number_of_cars
FROM customers c;

4. Derived Tables (Subqueries in the FROM Clause)

You can also place a subquery inside the FROM clause. In this context, the subquery acts as a temporary table (or derived table) that exists only during query execution. You must always assign an alias to a derived table:

SELECT managers.employee_id, employees.salary 
FROM (
    SELECT employee_id 
    FROM employees 
    WHERE manager_id IS NULL
) AS managers 
JOIN employees ON managers.employee_id = employees.employee_id;

Subquery vs. JOIN: Which is Better?

While subqueries are highly readable, they can sometimes be less efficient than JOIN operations on large databases. Here is how they compare:

Summary & Best Practices

In summary, use subqueries when you need to perform calculations beforehand (like average values) or check membership lists. However, if you need to fetch columns from both tables, prefer using JOINs. For more information on database-specific operations, read our guide on Commonly Used SQL Queries in MSSQL Server. You can also refer to the official Subquery Reference Page for more advanced examples.