Advanced Query Techniques

Beyond Basic Queries

You already know how to pull data, filter it, and join tables. But sometimes, the questions you need to answer are more complex. How do you find employees who earn more than their department's average salary? Or map out an entire organizational chart?

To tackle these challenges, you need to move beyond simple queries. We'll explore three powerful techniques for handling intricate data problems: subqueries, Common Table Expressions (CTEs), and window functions. These tools let you build queries layer by layer, turning a complicated request into a series of logical steps.

Queries Within Queries

A subquery, or inner query, is a SELECT statement nested inside another SQL statement. It's a way to perform a query in stages, where the result of the inner query is used by the outer query. There are a few different kinds.

Scalar Subqueries return a single value: one row, one column. You can use them almost anywhere you'd use a single value, like in a WHERE clause.

Imagine you want to find all products that are more expensive than the overall average price. You need to find the average price first, then use that value to filter the products table.

SELECT
  product_name,
  price
FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

The part in the parentheses is the scalar subquery. It calculates the average price, and the outer query uses that single number to find the pricey products.

Table Subqueries return a result set of multiple rows and columns, like a temporary table. You can use them in a FROM or JOIN clause.

Let's say you want a list of customers and the total amount they've spent. You could first create a temporary table of total spending per customer and then join it back to your customers table.

SELECT
  c.customer_name,
  s.total_spent
FROM customers c
JOIN (
  SELECT
    customer_id,
    SUM(order_total) AS total_spent
  FROM orders
  GROUP BY customer_id
) AS s ON c.customer_id = s.customer_id;

Here, the subquery calculates the spending for each customer, and the outer query joins that result to get their names.

Correlated Subqueries are a bit different. The inner query depends on the outer query for its values. It runs once for each row processed by the outer query.

This is useful for comparing a row to some property of its own group. For example, let's find employees whose salary is above the average for their specific department. The subquery needs to know which department to average for each employee.

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

Notice how the inner query (e2) references the outer query (e1). For every employee in the outer query, the inner query recalculates the average salary for that employee's department. While powerful, this can be slow on large tables because the subquery runs over and over.

Naming Your Steps with CTEs

As queries get more complex, nested subqueries can become hard to read. A Common Table Expression, or CTE, lets you break a query into simpler, logical steps. You define a temporary, named result set using a WITH clause, which you can then reference in your main query.

Think of it as giving a name to the result of a subquery.

CTEs simplify complex queries by breaking them into smaller, reusable subparts.

Let's rewrite the customer spending example using a CTE. It does the same thing, but the logic is easier to follow.

-- Define the CTE first
WITH CustomerSpending AS (
  SELECT
    customer_id,
    SUM(order_total) AS total_spent
  FROM orders
  GROUP BY customer_id
)

-- Now use the CTE in the main query
SELECT
  c.customer_name,
  cs.total_spent
FROM customers c
JOIN CustomerSpending cs ON c.customer_id = cs.customer_id;

The query is read from top to bottom. First, we define CustomerSpending. Then, we use it. This separation makes debugging and understanding the query much easier.

Let's find the entire reporting chain for an employee named 'David'. We'll assume an employees table with employee_id, employee_name, and manager_id.

WITH RECURSIVE OrgChart AS (
  -- Anchor Member: Start with David
  SELECT employee_id, employee_name, manager_id, 0 as level
  FROM employees
  WHERE employee_name = 'David'

  UNION ALL

  -- Recursive Member: Find people who report to the previous level
  SELECT e.employee_id, e.employee_name, e.manager_id, o.level + 1
  FROM employees e
  JOIN OrgChart o ON e.manager_id = o.employee_id
)

SELECT * FROM OrgChart;

The query starts with David, then finds everyone who reports to him, then everyone who reports to them, and so on, until it has mapped out his entire team structure.

A Window into Your Data

Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, they don't collapse rows; they return a value for each row based on a 'window' of related rows.

The window is defined using the OVER() clause. Inside, you can specify how to partition the data (PARTITION BY) and how to order it (ORDER BY).

Function	Description
`ROW_NUMBER()`	Assigns a unique, sequential integer to each row.
`RANK()`	Assigns a rank to each row; tied values get the same rank, and the next rank is skipped.
`LAG()`	Accesses data from a previous row in the window.
`LEAD()`	Accesses data from a subsequent row in the window.

Let's see them in action. Suppose we want to rank employees by salary within each department.

SELECT
  employee_name,
  department_name,
  salary,
  RANK() OVER (PARTITION BY department_name ORDER BY salary DESC) as salary_rank
FROM employees;

This query partitions the data by department, then orders the employees in each department by salary. The RANK() function then assigns a rank. The highest-paid person in Sales gets rank 1, and the highest-paid person in Engineering also gets rank 1.

Now, let's use LAG() to compare each employee's salary to the salary of the person ranked just above them in their department.

SELECT
  employee_name,
  department_name,
  salary,
  LAG(salary, 1, 0) OVER (PARTITION BY department_name ORDER BY salary DESC) AS previous_salary
FROM employees;

The LAG() function looks back one row (1) within the department partition, ordered by salary. If there is no previous row (i.e., for the top earner), it returns a default value of 0. This is great for calculating differences between consecutive rows, like month-over-month sales growth.

Quiz Questions 1/6

What is the primary characteristic of a correlated subquery?

Quiz Questions 2/6

Which SQL feature is best suited for querying hierarchical data, such as an organizational chart or a bill of materials?

Subqueries, CTEs, and window functions are essential for moving from basic data retrieval to sophisticated analysis. They provide the building blocks to construct queries that are not only powerful but also readable and maintainable.

Advanced SQL Mastery