Advanced SQL Querying

Beyond a Single Table

Querying a single table is a great start, but the real power of SQL comes from weaving together data from multiple tables. Businesses rarely store all their information in one giant spreadsheet. Instead, data is logically separated: customer information in one table, order details in another, and product specs in a third. To get a complete picture, like finding which products a specific customer ordered, you need to connect them.

This connection is made using JOINs. Think of tables as islands of data. JOINs are the bridges built between them, using shared columns, typically primary and foreign keys, to link related records. The most common bridge is the INNER JOIN.

An INNER JOIN returns only the rows where the key exists in both tables. If a customer has never placed an order, they won't appear in a query joining customers to orders.

SELECT
  c.customer_name,
  o.order_date,
  o.order_total
FROM customers c
INNER JOIN orders o
  ON c.customer_id = o.customer_id;

But what if you need to see all customers, including those who haven't ordered yet? This is where an OUTER JOIN, specifically a LEFT JOIN, comes in. It returns all rows from the left table (customers) and the matched rows from the right table (orders). If there's no match, the columns from the right table will be filled with NULL.

SELECT
  c.customer_name,
  o.order_date,
  o.order_total
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id;

The choice between INNER and OUTER joins depends entirely on the question you're asking. Are you interested only in the intersection of two datasets, or do you need to preserve the entirety of one? Getting this right is crucial for maintaining in your analysis.

Queries Within Queries

Sometimes, answering a question requires multiple steps. You might need to find a piece of information first and then use that information to filter a larger dataset. This is the job of a subquery, which is simply a query nested inside another query.

For example, let's find all orders placed by customers from California. You could first query the customers table to get the customer_id for everyone in California, and then use that list of IDs to filter the orders table. A subquery combines this into a single step.

SELECT
  order_id,
  order_date
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE state = 'CA'
);

The inner query runs first, returning a list of IDs. The outer query then uses this list to find the matching orders. While powerful, nesting multiple subqueries can make your code difficult to read and debug. For more complex logic, there's a cleaner way.

CTEs for Clarity

A (CTE) lets you create a temporary, named result set that you can reference within your main query. They are defined using a WITH clause at the beginning of your statement. Think of them as giving a nickname to a subquery's result.

Let's rewrite our California customer query using a CTE. Notice how the logic is separated into a clear, readable block. The main query at the end is simple and easy to understand.

WITH CaliforniaCustomers AS (
  SELECT customer_id
  FROM customers
  WHERE state = 'CA'
)

SELECT
  o.order_id,
  o.order_date
FROM orders o
JOIN CaliforniaCustomers cc
  ON o.customer_id = cc.customer_id;

The benefits become even clearer with multi-step logic. You can define multiple CTEs in a row, with each subsequent CTE able to reference the ones defined before it. This turns a tangled mess of nested subqueries into a clean, step-by-step recipe.

Advanced Calculations

Beyond just retrieving data, a key analyst skill is summarizing and ranking it. You're likely familiar with aggregate functions like COUNT() and SUM() paired with a GROUP BY clause. They're great for collapsing many rows into a single summary row, like calculating total sales per state.

GROUP BY reduces the number of rows in your output. Window functions perform calculations across a set of rows but return a value for every row.

Window functions are incredibly powerful. They let you answer questions like, "What is the running total of sales each day?" or "Who are the top 3 highest-spending customers in each state?"

A window function uses the OVER() clause to define the 'window' of rows to consider for its calculation. Let's rank employees by salary within each department.

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

Here, PARTITION BY department resets the ranking for each new department. This simple-looking query performs a complex task that would be very difficult to accomplish with joins or subqueries alone. Mastering window functions is a significant step toward becoming a proficient data analyst.

Quiz Questions 1/5

You have a customers table and an orders table. You need a list of only the customers who have placed at least one order. Which type of JOIN should you use?

Quiz Questions 2/5

What is a primary advantage of using a Common Table Expression (CTE) with the WITH clause instead of a complex nested subquery?

Putting these tools together—JOINs for combining data, CTEs for organizing logic, and window functions for sophisticated analysis—forms the core of advanced SQL querying. With these skills, you can tackle complex analytical questions with clean, efficient, and readable code.

Path to Professional Data Analysis