Complex Data Transformation

Beyond Basic Queries

You already know how to pull data with SELECT, filter it with WHERE, and connect tables with a simple INNER JOIN. Now, it's time to move from just retrieving data to strategically reshaping it. Professional data roles require transforming messy, multi-table datasets into clean, analysis-ready formats.

This is where advanced SQL techniques come in. We'll focus on tools that make complex logic manageable, handle tricky hierarchical relationships, and identify crucial gaps in your data. Mastering these will prepare you for the kind of complex data puzzles you'll face daily.

Taming Complexity with CTEs

As queries grow, they can become a tangled mess of nested subqueries, making them difficult to read, debug, and maintain. The solution is the Common Table Expression (CTE), a temporary, named result set that you can reference within a larger query. Think of it as giving a name to a step in a multi-part recipe. It doesn't change the ingredients, but it makes the instructions much easier to follow.

A CTE is defined using the WITH keyword. Let's look at an example. Imagine we need to find all orders with a total value greater than the average order value across all customers.

-- Without a CTE, using a subquery
SELECT
    order_id,
    SUM(order_value) AS total_value
FROM orders
GROUP BY order_id
HAVING SUM(order_value) > (
    SELECT AVG(total_order_value)
    FROM (
        SELECT SUM(order_value) AS total_order_value
        FROM orders
        GROUP BY order_id
    ) AS order_totals
);

That nested subquery is already hard to follow. Now, let's refactor it using a CTE to calculate the order totals first.

-- With a CTE for clarity
WITH OrderTotals AS (
    -- First, calculate the total for each order
    SELECT
        order_id,
        SUM(order_value) AS total_value
    FROM orders
    GROUP BY order_id
)
-- Now, use the CTE in the main query
SELECT
    order_id,
    total_value
FROM OrderTotals
WHERE total_value > (SELECT AVG(total_value) FROM OrderTotals);

The logic is identical, but the CTE version is cleaner and self-documenting. You can define multiple CTEs in a sequence, with later ones referencing earlier ones, to break down a complex problem into a series of logical, manageable steps.

Joins for Tricky Relationships

Standard INNER and LEFT joins cover most cases, but some data structures require more specialised tools. Let's explore joins that handle hierarchical data and help you spot inconsistencies between tables.

Self Joins: Uncovering Hierarchies

A self join is when you join a table to itself. This sounds strange, but it's the standard way to handle hierarchical data stored in a single table. The classic example is an employees table where one column, like manager_id, refers to another employee's employee_id in the same table.

To make this work, you must use table aliases to treat the single table as two distinct entities in the query.

-- Create an employees table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT -- This links to another employee_id
);

-- Find the manager for each employee
SELECT
    e.name AS employee_name, -- e for employee
    m.name AS manager_name   -- m for manager
FROM
    employees e
LEFT JOIN
    employees m ON e.manager_id = m.employee_id;

We use a LEFT JOIN here instead of an INNER JOIN to ensure that employees without a manager (like the CEO) are still included in the result set. Their manager_name will simply be NULL.

FULL OUTER JOIN: Finding the Gaps

What if you need to compare two tables and see everything from both, regardless of whether a match exists? An INNER JOIN only shows matches. A LEFT JOIN shows everything from the left table and matches from the right. A FULL OUTER JOIN shows every row from both tables.

This is incredibly useful for gap analysis—finding records that exist in one table but not the other. For instance, comparing a table of warehouse_stock with sales_records could reveal products that are in stock but have never sold, or products that have been sold but show zero stock (a potential data error).

SELECT
    COALESCE(ws.product_id, sr.product_id) AS product_id,
    ws.quantity_on_hand,
    sr.units_sold
FROM
    warehouse_stock ws
FULL OUTER JOIN
    sales_records sr ON ws.product_id = sr.product_id
WHERE
    ws.product_id IS NULL OR sr.product_id IS NULL;

The WHERE clause here isolates the mismatches. Rows where ws.product_id is NULL are products that were sold but aren't in the stock table. Rows where sr.product_id is NULL are products in stock that have never been sold. We use COALESCE to display the product_id from whichever table has it.

Handling NULLs and Optimisation

As queries get more complex, so does handling missing data. A NULL value doesn't equal zero or an empty string; it represents an unknown. This has important implications for your logic.

When writing conditional logic with CASE statements or filters, always test for NULL explicitly using IS NULL or IS NOT NULL. A common mistake is to write WHERE column = NULL, which will never evaluate to true. Functions like COALESCE(value1, value2, ...) and NULLIF(value1, value2) are your best friends for substituting NULLs or generating them when two values are equal.

A Note on Optimisation

Writing a query that works is one thing. Writing one that runs efficiently on millions of rows is another. While deep query optimisation is a field in itself, a few principles go a long way:

Index Strategically: Ensure columns used in JOIN conditions and WHERE clauses are indexed. This is the single biggest performance booster.
Filter Early: Apply WHERE clauses as early as possible to reduce the number of rows processed in later steps.
Avoid SELECT *: Only select the columns you actually need. This reduces data transfer and can sometimes allow the database to use more efficient index-only scans.
Understand the Plan: Use EXPLAIN (or EXPLAIN PLAN) to see how the database intends to execute your query. This can reveal bottlenecks, like a table scan where you expected an index seek.

With these advanced transformation techniques, you can now structure, combine, and clean data with far greater precision and clarity. You're ready to tackle the complex, multi-layered datasets that underpin modern data analysis.

Ready to test your skills?

Quiz Questions 1/6

What is the primary benefit of using a Common Table Expression (CTE) in a complex SQL query?

Quiz Questions 2/6

You have a single table named Employees with columns EmployeeID, EmployeeName, and ManagerID. The ManagerID for each employee corresponds to the EmployeeID of their manager. Which type of join is most suitable for retrieving a list of all employees alongside their manager's name from this single table?

Mastering Modern Data Analytics