Advanced SQL Queries

Beyond Simple Queries

You've mastered the basics of fetching and filtering data. But what happens when the question you're asking is more complex? What if you need to use the result of one query to inform another, or perform calculations across a specific set of rows? For these scenarios, you need more powerful tools. Let's explore three advanced techniques that will elevate your SQL skills: subqueries, Common Table Expressions (CTEs), and window functions.

Subqueries for Nested Logic

A subquery, also known as an inner query or nested query, is a query placed inside another SQL query. It's a way to break down a complex problem into smaller, logical steps. The outer query uses the result of the inner query to determine what data to return.

Imagine you have two tables: Employees and Departments. You want to find all employees who work in the 'Sales' department, but you only have the department name, not its ID. You can use a subquery to find the ID first.

SELECT 
    FirstName, 
    LastName, 
    Salary
FROM 
    Employees
WHERE 
    DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');

Here, the inner query (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales') runs first. It finds the DepartmentID for 'Sales'. The outer query then takes that resulting ID and uses it to filter the Employees table.

Subqueries are versatile and can be placed in WHERE, FROM, and even SELECT clauses. However, nesting multiple subqueries can make your code difficult to read and debug. That's where CTEs come in.

Cleaning Up with CTEs

A Common Table Expression, or CTE, is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs dramatically improve the readability and structure of complex queries. You define a CTE using the WITH clause.

Let's rewrite our previous subquery example using a CTE.

WITH SalesDepartment AS (
    SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales'
)
SELECT 
    e.FirstName, 
    e.LastName, 
    e.Salary
FROM 
    Employees e
JOIN 
    SalesDepartment sd ON e.DepartmentID = sd.DepartmentID;

We first create a temporary result set called SalesDepartment that holds the ID for the sales department. Then, in the main query, we simply join the Employees table with our new SalesDepartment CTE. The logic is identical, but the structure is much cleaner. Each logical step is separated, making the query easier to understand and maintain.

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

Window Functions for Advanced Analysis

Window functions are a powerful feature for performing calculations across a set of table rows that are related to the current row. Unlike aggregate functions like SUM() or AVG() which collapse rows into a single output, window functions perform a calculation for each row based on a "window" of related rows.

This is incredibly useful for tasks like calculating running totals, moving averages, or ranking results within categories.

All window functions use the OVER() clause. This clause does two things:

PARTITION BY: Divides the rows into partitions, or groups. The window function is applied independently to each partition.
ORDER BY: Orders the rows within each partition. This is crucial for functions that depend on order, like running totals or ranking.

Ranking Functions

Ranking functions are a common type of window function. They assign a rank to each row within a partition based on a specified ordering. The main ranking functions are:

ROW_NUMBER(): Assigns a unique, sequential integer to each row. No ties.
RANK(): Assigns a rank to each row. If rows have the same value, they get the same rank, and the next rank is skipped. (e.g., 1, 2, 2, 4)
DENSE_RANK(): Similar to RANK(), but doesn't skip ranks after a tie. (e.g., 1, 2, 2, 3)

Let's rank employees by their salary within each department.

SELECT
    FirstName,
    LastName,
    DepartmentName,
    Salary,
    RANK() OVER (PARTITION BY DepartmentName ORDER BY Salary DESC) as SalaryRank
FROM
    Employees e
JOIN
    Departments d ON e.DepartmentID = d.DepartmentID;

In this query, PARTITION BY DepartmentName creates a separate "window" for each department. Within each department's window, ORDER BY Salary DESC sorts employees from highest to lowest salary. The RANK() function then assigns a rank based on that order. The highest earner in 'Sales' gets rank 1, and the highest earner in 'Engineering' also gets rank 1.

Aggregations with Window Functions

You can also use familiar aggregate functions like SUM(), AVG(), and COUNT() as window functions. This allows you to, for example, calculate a running total or compare an individual row's value to the average of its group.

Let's calculate a running total of salaries for new hires within each month, ordered by their hire date.

SELECT
    HireDate,
    FirstName,
    LastName,
    Salary,
    SUM(Salary) OVER (ORDER BY HireDate) as RunningTotalSalary
FROM
    Employees;

Here, the window is all rows ordered by HireDate. For each employee, SUM(Salary) calculates the sum of their salary and the salaries of all employees hired before them. Because we omitted PARTITION BY, the window is the entire result set.

If we wanted to see this running total reset for each department, we would add PARTITION BY DepartmentID to the OVER() clause.

Quiz Questions 1/5

What is the primary advantage of using a Common Table Expression (CTE) over a complex, nested subquery?

Quiz Questions 2/5

Which statement best describes the fundamental difference between a window function and a standard aggregate function like SUM() or AVG()?

These advanced techniques open up a new level of analytical capability. They allow you to write cleaner, more powerful queries to answer complex business questions directly within the database.

SQL Mastery for Job Readiness