Advanced SQL Queries

Beyond the Basics

You've already seen how to pull data from tables using SELECT, filter it with WHERE, and sort it with ORDER BY. These are the building blocks of SQL. Now, let's explore more advanced ways to combine and analyze data. Real-world questions often require pulling information from multiple tables and performing calculations that go beyond simple aggregates. This is where tools like subqueries, advanced joins, and window functions come into play.

Queries Within Queries

Sometimes, to answer a question, you first need to answer another question. For example, to find all employees who earn more than the average salary, you first need to find out what the average salary is. You can do this in SQL by placing one query inside another. This is called a subquery.

A subquery is a SELECT statement nested inside another statement. The inner query runs first, and its result is used by the outer query.

Let's find those employees who earn more than the average. We can use one query to calculate the average salary and another to find the employees.

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

Here, (SELECT AVG(salary) FROM employees) is the subquery. The database calculates the average salary first, and then the outer query uses that single value to filter the employees table.

A more complex type is a correlated subquery. Unlike a simple subquery, a correlated subquery depends on the outer query for its values. It's evaluated once for each row processed by the outer query. This can be powerful but is often less efficient.

Imagine you want to find all employees in each department whose salary is above their department's average. The subquery needs to know which department's average to calculate for each employee.

SELECT first_name, last_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) refers to the outer query (e1) with e2.department_id = e1.department_id. For every single row in the outer employees table, the inner query recalculates the average salary for that specific department.

Joining and Combining Data

Joins are essential for combining rows from two or more tables based on a related column. While INNER JOIN is common, several other types of joins give you more control over your results.

Here's a quick rundown:

INNER JOIN: Returns records that have matching values in both tables. This is the most common join.
LEFT JOIN: Returns all records from the left table, and the matched records from the right table. If there's no match, the result is NULL on the right side.
RIGHT JOIN: Returns all records from the right table, and the matched records from the left table. If there's no match, the result is NULL on the left side.
FULL OUTER JOIN: Returns all records when there is a match in either the left or the right table. It combines the functionality of both LEFT JOIN and RIGHT JOIN.

While joins combine columns from different tables, set operations combine rows from different queries. The queries must have the same number of columns with similar data types.

Operator	Description
`UNION`	Combines the result sets of two queries and removes duplicate rows.
`UNION ALL`	Combines the result sets but includes all duplicate rows.
`INTERSECT`	Returns only the rows that appear in both result sets.
`EXCEPT`	Returns the rows from the first query that are not present in the second query.

For example, if you had a list of CurrentEmployees and a list of FormerEmployees, you could use UNION to get a single list of everyone who has ever worked for the company.

Simplifying Complexity

As queries grow, they can become difficult to read and manage, especially with multiple levels of nested subqueries. Common Table Expressions, or CTEs, help solve this problem. A CTE lets you define a temporary, named result set that you can reference within your main query. You create a CTE using the WITH clause.

Think of a CTE as giving a nickname to a complex subquery, making your code cleaner and more organized.

Let's rewrite our correlated subquery from before using a CTE. First, we'll create a CTE that calculates the average salary for each department. Then, we'll join our main employees table to this CTE.

WITH DepartmentAverages AS (
  SELECT department_id, AVG(salary) as avg_dept_salary
  FROM employees
  GROUP BY department_id
)
SELECT e.first_name, e.last_name, e.salary, da.avg_dept_salary
FROM employees e
JOIN DepartmentAverages da ON e.department_id = da.department_id
WHERE e.salary > da.avg_dept_salary;

This version is much easier to follow. The logic is broken into logical, reusable steps.

Another powerful tool for complex analysis are window functions. A window function performs a calculation across a set of table rows that are somehow related to the current row. This is different from an aggregate function, which groups rows into a single output row. A window function returns a value for every single row.

You need advanced features like window functions, CTEs, custom data types, or PostGIS for geospatial queries.

Window functions are great for ranking, calculating running totals, or finding moving averages. They use an OVER() clause to define the 'window' of rows to operate on. Some common ranking functions include:

ROW_NUMBER(): Assigns a unique number to each row.
RANK(): Assigns a rank to each row, with gaps in the ranking for ties.
DENSE_RANK(): Assigns a rank without gaps. Ties get the same rank.
NTILE(N): Divides rows into a specified number of ranked groups (e.g., quartiles).

Score	ROW_NUMBER()	RANK()	DENSE_RANK()
95	1	1	1
95	2	1	1
90	3	3	2
88	4	4	3

In the table above, two scores are tied at 95. RANK() gives them both rank 1, but then skips to rank 3 for the next score. DENSE_RANK() also gives them rank 1 but continues with rank 2 for the next score. ROW_NUMBER() just keeps counting regardless of ties.

Here's how you might use RANK() to find the rank of employees based on salary within each department.

SELECT
  first_name,
  last_name,
  department_id,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank
FROM employees;

The PARTITION BY department_id clause resets the ranking for each new department, effectively creating a separate 'window' for each one.

Ready to test your knowledge?

Quiz Questions 1/6

What is the primary characteristic of a correlated subquery?

Quiz Questions 2/6

You have a Customers table and an Orders table. You need a list of all customers, including those who have never placed an order. Which type of join should you use, with Customers as the left table?

Mastering these advanced techniques will allow you to extract powerful insights from your data. They transform SQL from a simple data retrieval tool into a robust analytical engine.

Advanced SQL Mastery