Advanced Query Techniques

Beyond Simple SELECTs

You've already mastered the art of pulling data with SELECT, filtering it with WHERE, and sorting it with ORDER BY. But real-world data problems often require more nuance. You might need to compare a row's value to an aggregate of other rows, or organize a query in logical, readable steps. Advanced techniques like subqueries, CTEs, and window functions unlock this next level of data manipulation.

Queries Within Queries

A subquery, or inner query, is a query nested inside another SQL statement. It's a powerful tool for breaking down complex problems. You can place a subquery in a SELECT, FROM, WHERE, or HAVING clause, and its result is used by the outer query.

Let’s say you have an employees table and you want to find everyone who earns more than the company average. You can't just write WHERE salary > AVG(salary) because aggregate functions don't work that way in a WHERE clause. A subquery solves this elegantly.

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

The inner query (SELECT AVG(salary) FROM employees) runs first, calculating the average salary. The outer query then uses this single value to filter the employees table.

Subqueries can also be correlated. A correlated subquery depends on the outer query for its values. It's evaluated once for each row processed by the outer query. For instance, to find employees who are the highest earners in their respective departments:

SELECT
  employee_name,
  salary,
  department_id
FROM employees e1
WHERE salary = (
  SELECT MAX(salary) 
  FROM employees e2
  WHERE e2.department_id = e1.department_id
);

Here, the inner query calculates the max salary for the current row's department (e1.department_id). This can be less efficient than other methods, as the subquery may run repeatedly.

Making Queries Readable with CTEs

When subqueries get complex or you need to reference the same temporary result set multiple times, your code can become difficult to read and maintain. Common Table Expressions (CTEs) offer a cleaner solution.

A CTE lets you define a temporary, named result set that exists only for the duration of a single query. You define it using the WITH clause. Let's rewrite our first subquery example using a CTE.

WITH AverageSalary AS (
  SELECT AVG(salary) AS avg_sal
  FROM employees
)
SELECT
  e.employee_name,
  e.salary
FROM employees e, AverageSalary av
WHERE e.salary > av.avg_sal;

The logic is the same, but the structure is different. We first define AverageSalary, then we use it in the main SELECT statement. This separation makes the query's purpose clearer. Each logical step gets its own named block.

Use CTEs for Complex Queries Break down queries into readable blocks, reducing maintenance overhead.

A New Window on Your Data

Aggregate functions like SUM() and COUNT() are great, but they operate on an entire group of rows defined by GROUP BY, collapsing them into a single output row. What if you want to perform a calculation across a set of rows but still keep the individual rows?

This is where window functions come in. They operate on a "window" of rows related to the current row, defined by the OVER() clause. This allows you to compute running totals, rankings, and moving averages without losing detail.

The key is the OVER() clause. It tells the database which set of rows to consider for the calculation.

The OVER() clause has two main components:

PARTITION BY: This divides the rows into partitions, or groups. The window function is applied independently to each partition. It’s like a GROUP BY but for window functions.
ORDER BY: This sorts the rows within each partition. This is crucial for functions that depend on order, like calculating a running total or a rank.

Let's use ROW_NUMBER() to rank employees by salary within each department.

SELECT 
  employee_name,
  department_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank_in_dept
FROM employees;

This query assigns a unique, sequential rank to each employee based on their salary, but the ranking restarts for each new department. ROW_NUMBER() is just one of many window functions. RANK() and DENSE_RANK() are also used for ranking, but they handle ties differently.

Function	Behavior with Ties (e.g., two people tied for 2nd)
`ROW_NUMBER()`	Assigns a unique number to each row, arbitrarily breaking ties.
`RANK()`	Assigns the same rank to tied rows, then skips the next rank(s).
`DENSE_RANK()`	Assigns the same rank to tied rows, but does not skip ranks.

Navigating Hierarchies with Recursion

Some datasets have a hierarchical or tree-like structure, such as an organizational chart where each employee reports to a manager, who in turn reports to another manager. How do you query this kind of self-referencing relationship when you don't know how many levels deep the hierarchy goes?

A recursive query, built using a recursive CTE, is the answer. It's a CTE that references itself, allowing it to loop through the hierarchy level by level.

A recursive CTE has two parts:

Anchor Member: A SELECT statement that provides the starting point for the recursion (e.g., the top-level employee, like the CEO).
Recursive Member: A SELECT statement that joins back to the CTE itself to find the next level of the hierarchy. This part is joined to the anchor with a UNION ALL.

Here's how to list all employees under the 'VP of Sales' (ID 2).

WITH RECURSIVE Subordinates AS (
  -- Anchor Member: Start with the VP of Sales
  SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    1 AS hierarchy_level
  FROM employees
  WHERE employee_id = 2

  UNION ALL

  -- Recursive Member: Find employees who report to the previous level
  SELECT 
    e.employee_id, 
    e.employee_name, 
    e.manager_id, 
    s.hierarchy_level + 1
  FROM employees e
  INNER JOIN Subordinates s ON e.manager_id = s.employee_id
)
SELECT * FROM Subordinates;

The query starts with the employee whose ID is 2. Then, it repeatedly finds all employees whose manager_id matches an employee_id already in the Subordinates result set, continuing until it can't find any more direct reports. The hierarchy_level column tracks how far down the chain each person is.

Quiz Questions 1/5

What is the primary purpose of using a subquery within a WHERE clause?

Quiz Questions 2/5

Which of the following best describes the main advantage of using a Common Table Expression (CTE) over a complex, nested subquery?

These advanced techniques are the building blocks for tackling sophisticated data analysis and manipulation tasks. Mastering them will significantly expand what you can accomplish with SQL.

SQL for Entry-Level Job Readiness