Subqueries

Queries Within Queries

Sometimes, answering one question requires you to answer another question first. If you want to find all employees who work in the same city as the company headquarters, you first need to figure out where the headquarters is. SQL lets you do this in a single step using a subquery, also known as a nested query or inner query.

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

The database runs the inner query first. The result of this inner query is then used by the outer query to finish the job. This lets you build powerful, dynamic queries that can filter, calculate, and retrieve data in more complex ways than a simple SELECT statement allows.

Filtering with Subqueries

The most common place to use a subquery is in the WHERE clause. This allows you to filter the results of the main query based on the data returned by the subquery.

Let's say we want to find every employee who works in the same department as an employee named 'Laura'. We don't know Laura's department ID, but we can find it with a query. We can nest that query inside our main query.

SELECT first_name, last_name, department_id
FROM employees
WHERE department_id = (
  SELECT department_id 
  FROM employees 
  WHERE first_name = 'Laura'
);

The database first executes the subquery (SELECT department_id ...) which returns a single value, say 50. The main query then becomes ... WHERE department_id = 50, returning all employees from that department.

This is a single-row subquery because it returns just one row. You can use standard comparison operators like =, >, or < with it.

But what if your subquery returns multiple rows? For example, let's find all employees who work in a department located in the UK. First, we need to find all department IDs for locations in the UK.

SELECT employee_id, first_name
FROM employees
WHERE department_id IN (
  SELECT department_id 
  FROM departments d
  JOIN locations l ON d.location_id = l.location_id
  WHERE l.country_id = 'UK'
);

This is a multi-row subquery. The inner query might return several department IDs. Because of this, we can't use the = operator. Instead, we use operators designed for lists, like IN, which checks if a value exists within the set of results returned by the subquery. Other operators you can use here include NOT IN, ANY, and ALL.

Advanced Subquery Placements

Subqueries aren't limited to the WHERE clause. You can place them in other parts of your statement to achieve different goals.

Using a subquery in the FROM clause creates a temporary, virtual table that exists only for the duration of the query. This is called a derived table.

Imagine you want to see each employee's salary alongside the average salary for their specific department. You can calculate the departmental averages in a subquery and then join the main employees table to this derived table.

SELECT e.first_name, e.salary, dept_avg.avg_salary
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department_id
) AS dept_avg ON e.department_id = dept_avg.department_id;

You can also use a subquery in the SELECT list. This is called a scalar subquery, and it must return exactly one row and one column, a single scalar value. This is useful for grabbing a related piece of information for each row in your main result set.

SELECT 
  employee_id, 
  first_name, 
  (SELECT department_name FROM departments d WHERE d.department_id = e.department_id) AS dept_name
FROM employees e;

In this example, for every employee returned by the outer query, the scalar subquery runs to fetch the corresponding department name. This can be handy, but be careful. If the subquery could return more than one value, your query will fail.

This brings us to a special type of subquery.

Correlated Subqueries

Notice in the last example, the inner query depends on the outer query. It uses e.department_id, where e is the table from the outer query. This is a correlated subquery.

Unlike a regular subquery that runs once, a correlated subquery is executed once for each row processed by the outer query. Think of it as a loop. The outer query gets a row, passes a value from that row to the inner query, the inner query runs, returns its result, and then the outer query moves to the next row.

Correlated subqueries are powerful but can be slow because the inner query runs repeatedly. Often, a JOIN is a more efficient alternative.

Here's an example to find all employees whose salary is above the average for their department. We've already seen how to do this with a derived table, but a correlated subquery is another way.

SELECT first_name, salary, department_id
FROM employees e1
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

For each employee (e1) in the outer query, the inner query calculates the average salary for that specific employee's department (e1.department_id). The outer query then compares the employee's salary to that average. While this works perfectly, the JOIN approach we saw earlier would likely perform better on a large dataset.

Time to test your knowledge.

Quiz Questions 1/5

What is the defining characteristic of a correlated subquery?

Quiz Questions 2/5

Which operator is most appropriate for a WHERE clause that compares a column to the results of a multi-row subquery?

Subqueries are a fundamental tool in SQL, enabling you to solve complex problems by breaking them down into logical steps within a single statement.

SQL Server Advanced Querying