Multi-Table and Advanced SQL Queries

Introduction

Relational databases become analytically valuable when information distributed across several tables can be combined, compared, ranked, reshaped, and explored recursively. A customer table may contain demographic information, an orders table may record transactions, and a products table may describe items sold. Useful business questions—such as “Which customers generated the greatest profit in each region?”—usually require more than a simple single-table query.

This chapter develops the principal SQL techniques used for complex analytical work: joins, set operations, subqueries, common table expressions, window functions, pivots, and recursive queries. Examples use broadly portable SQL, although syntax for pivoting, date functions, and recursive features varies among PostgreSQL, MySQL, SQL Server, Oracle, and cloud data platforms.

Learning Objectives

By the end of this chapter, you should be able to:

• combine related tables using appropriate join types;
• distinguish row-preserving joins from filtering operations;
• use UNION, INTERSECT, and EXCEPT safely;
• construct correlated and non-correlated subqueries;
• organize multi-stage analysis with common table expressions;
• calculate rankings, running totals, and period comparisons with window functions;
• transform rows into columns using pivot techniques;
• query hierarchical data with recursive common table expressions;
• assess performance, correctness, and governance risks in advanced SQL.

1. A Relational View of Multi-Table Analysis

A relational database represents entities and relationships through tables. A primary key uniquely identifies a row, while a foreign key refers to a key in another table. For example:

• Customers(customer_id, customer_name, region)
• Orders(order_id, customer_id, order_date, status)
• OrderLines(order_id, product_id, quantity, unit_price)
• Products(product_id, product_name, category, cost)

An analytical query often follows this logical sequence:

1. identify the required entities;
2. join tables through valid keys;
3. filter rows;
4. group or calculate measures;
5. apply window or ranking logic;
6. order or present the result.

SQL’s written order is not identical to its conceptual processing order. In simplified form, SQL evaluates FROM and JOIN, then WHERE, GROUP BY, aggregate calculations, HAVING, window expressions, SELECT, DISTINCT, ORDER BY, and finally LIMIT or FETCH. Understanding this order explains why a window function normally cannot be referenced directly in WHERE and why a subquery or CTE is needed.

2. Joins

2.1 Inner joins

An inner join returns only rows for which the join condition matches in both tables.

SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM Customers AS c
JOIN Orders AS o
  ON o.customer_id = c.customer_id;

If a customer has no order, that customer is excluded. Inner joins are appropriate when the analysis concerns only entities with corresponding records.

A frequent error is joining on a non-unique attribute such as customer name. Joins should normally use stable keys. If one customer row matches three order rows, the customer appears three times; this is not duplication caused by SQL but the expected result of a one-to-many relationship.

2.2 Outer joins

A left join preserves every row from the left table and adds matching values from the right table. Unmatched right-side columns become NULL.

SELECT c.customer_id, c.customer_name, o.order_id
FROM Customers AS c
LEFT JOIN Orders AS o
  ON o.customer_id = c.customer_id;

This query can identify customers with no orders:

SELECT c.customer_id, c.customer_name
FROM Customers AS c
LEFT JOIN Orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

A right join is equivalent in principle but reverses table roles. A full outer join preserves unmatched rows from both sides where supported.

The location of a filter matters. Compare:

-- Preserves customers, but only matches completed orders
SELECT c.customer_id, o.order_id
FROM Customers AS c
LEFT JOIN Orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'Completed';

with:

-- Removes customers without a completed order
SELECT c.customer_id, o.order_id
FROM Customers AS c
LEFT JOIN Orders AS o
  ON o.customer_id = c.customer_id
WHERE o.status = 'Completed';

The second query behaves like an inner join for this condition because rows with NULL status fail the WHERE predicate.

2.3 Cross joins and self-joins

A cross join forms every possible pair of rows. If table A contains m rows and table B contains n rows, the result contains m × n rows, assuming no subsequent filtering. Cross joins are useful for creating a complete calendar–product grid, but dangerous when accidental.

A self-join joins a table to itself. It is useful for employee-supervisor relationships:

SELECT e.employee_name,
       m.employee_name AS manager_name
FROM Employees AS e
LEFT JOIN Employees AS m
  ON e.manager_id = m.employee_id;

2.4 Joining and aggregation

To calculate revenue by region:

SELECT c.region,
       SUM(ol.quantity * ol.unit_price) AS revenue
FROM Customers AS c
JOIN Orders AS o ON o.customer_id = c.customer_id
JOIN OrderLines AS ol ON ol.order_id = o.order_id
WHERE o.status = 'Completed'
GROUP BY c.region;

The expression quantity × unit_price is a line-level measure. Aggregating before joining all required detail can produce incomplete or misleading results. Conversely, joining multiple independent one-to-many tables can multiply rows and inflate totals. Pre-aggregate each independent detail source when necessary.

3. Set Operations

Set operations combine compatible query results vertically. Corresponding columns must be compatible in number and generally compatible in data type.

Example:

SELECT customer_id FROM Orders WHERE order_date >= DATE '2025-01-01'
UNION
SELECT customer_id FROM SupportTickets WHERE opened_at >= TIMESTAMP '2025-01-01 00:00:00';

UNION ALL is usually faster because it does not perform duplicate elimination. Use it when duplicate rows are meaningful or when the inputs are known to be mutually exclusive.

Set operations differ from joins. A join places columns side by side according to a relationship; a set operation places rows from compatible result sets one below another. An ORDER BY generally belongs at the end of the complete set expression.

4. Subqueries

A subquery is a query nested inside another query. A scalar subquery returns one value; a multi-row subquery returns a set of values; a correlated subquery refers to a column from the outer query.

4.1 Scalar and multi-row subqueries

The following finds products priced above the overall average:

SELECT product_id, product_name, list_price
FROM Products
WHERE list_price > (SELECT AVG(list_price) FROM Products);

The subquery must return exactly one value in this context. For membership, use IN:

SELECT customer_id, customer_name
FROM Customers
WHERE customer_id IN (
    SELECT customer_id
    FROM Orders
    WHERE status = 'Completed'
);

EXISTS tests whether at least one matching row exists and is often clearer for existence questions:

SELECT c.customer_id, c.customer_name
FROM Customers AS c
WHERE EXISTS (
    SELECT 1
    FROM Orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'Completed'
);

NOT IN requires care when the subquery can return NULL; three-valued logic may cause unexpected results. NOT EXISTS is generally safer for anti-matching logic.

4.2 Correlated subqueries

A correlated subquery is evaluated conceptually for each candidate outer row. The following finds customers whose completed order count exceeds the average customer order count, although a CTE is often more readable for this task:

SELECT c.customer_id, c.customer_name
FROM Customers AS c
WHERE (
    SELECT COUNT(*)
    FROM Orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'Completed'
) > 5;

Correlated subqueries can be expressive, but performance depends on the optimizer, indexes, data volume, and query shape. Equivalent joins or pre-aggregated CTEs may be more efficient.

5. Common Table Expressions

A common table expression (CTE) gives a temporary name to a query defined with WITH. It exists for the duration of one statement and improves readability by separating logical stages.

WITH CustomerRevenue AS (
    SELECT o.customer_id,
           SUM(ol.quantity * ol.unit_price) AS revenue
    FROM Orders AS o
    JOIN OrderLines AS ol ON ol.order_id = o.order_id
    WHERE o.status = 'Completed'
    GROUP BY o.customer_id
)
SELECT c.customer_name, cr.revenue
FROM CustomerRevenue AS cr
JOIN Customers AS c ON c.customer_id = cr.customer_id
WHERE cr.revenue > 10000;

Multiple CTEs can form a pipeline:

WITH LineRevenue AS (
    SELECT order_id, quantity * unit_price AS revenue
    FROM OrderLines
), OrderRevenue AS (
    SELECT order_id, SUM(revenue) AS revenue
    FROM LineRevenue
    GROUP BY order_id
), CustomerRevenue AS (
    SELECT o.customer_id, SUM(r.revenue) AS revenue
    FROM Orders AS o
    JOIN OrderRevenue AS r ON r.order_id = o.order_id
    WHERE o.status = 'Completed'
    GROUP BY o.customer_id
)
SELECT * FROM CustomerRevenue;

A CTE is not automatically a materialized table. The optimizer may inline it, materialize it, or choose another strategy. Therefore, CTEs primarily express logic; performance must be tested with an execution plan.

6. Window Functions

A window function calculates across related rows without collapsing them into one row per group. Its general structure is:

function(...) OVER (
    PARTITION BY grouping_columns
    ORDER BY sequencing_columns
    [frame specification]
)

PARTITION BY defines independent groups. ORDER BY defines sequence within each group. A frame, when specified, defines which rows around the current row are included.

6.1 Ranking

SELECT region, customer_id, revenue,
       RANK() OVER (
           PARTITION BY region
           ORDER BY revenue DESC
       ) AS regional_rank
FROM CustomerRevenue;

ROW_NUMBER() assigns a unique sequence, even when values tie. RANK() gives tied rows the same rank and leaves gaps after ties. DENSE_RANK() gives tied rows the same rank without gaps.

6.2 Running totals and moving averages

SELECT customer_id, order_date, revenue,
       SUM(revenue) OVER (
           PARTITION BY customer_id
           ORDER BY order_date, order_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_revenue
FROM CustomerOrders;

The explicit ROWS frame is important when dates repeat. A RANGE frame may treat all peer rows with the same ordering value as one group, depending on the database system.

A three-row moving average can be written as:

AVG(revenue) OVER (
    PARTITION BY customer_id
    ORDER BY order_date, order_id
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

The first rows use fewer than three observations unless the query separately enforces a complete window.

6.3 Comparing periods

LAG and LEAD access preceding and following rows:

SELECT month_start, revenue,
       LAG(revenue) OVER (ORDER BY month_start) AS prior_revenue,
       revenue - LAG(revenue) OVER (ORDER BY month_start) AS change
FROM MonthlyRevenue;

If prior revenue is not zero, percentage change is commonly calculated as:

The formula assumes the prior value is nonzero and that the units of both values are the same. A zero or missing prior value requires a defined business rule rather than silent division.

7. Pivoting and Conditional Aggregation

A pivot changes values from rows into columns. Some systems support PIVOT directly; portable SQL commonly uses conditional aggregation:

SELECT region,
       SUM(CASE WHEN category = 'Electronics' THEN revenue ELSE 0 END) AS electronics_revenue,
       SUM(CASE WHEN category = 'Furniture' THEN revenue ELSE 0 END) AS furniture_revenue,
       SUM(revenue) AS total_revenue
FROM RegionalCategoryRevenue
GROUP BY region;

This approach is explicit and portable, but the column list must be changed when categories change. Dynamic pivoting uses database-specific dynamic SQL and requires careful validation because identifiers cannot safely be treated like ordinary data parameters.

An unpivot operation converts columns back into rows. Its implementation also varies by database and is often achieved through UNION ALL or a vendor-specific operator.

8. Recursive Queries and Hierarchies

Hierarchical data includes organizational structures, product assemblies, folder trees, and bill-of-materials systems. A recursive CTE has an anchor member and a recursive member:

WITH RECURSIVE OrgTree AS (
    SELECT employee_id,
           employee_name,
           manager_id,
           0 AS level,
           CAST(employee_name AS VARCHAR(500)) AS path
    FROM Employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id,
           e.employee_name,
           e.manager_id,
           t.level + 1,
           CONCAT(t.path, ' > ', e.employee_name)
    FROM Employees AS e
    JOIN OrgTree AS t
      ON e.manager_id = t.employee_id
)
SELECT * FROM OrgTree;

The anchor identifies top-level employees. Each recursive iteration finds direct reports. A robust production query should address cycles, maximum depth, missing parents, and duplicate paths. Syntax differs: for example, some SQL Server queries omit the RECURSIVE keyword, while PostgreSQL includes it.

9. Integrated Worked Example

Suppose an online retailer wants the top two customers by completed revenue in each region, together with each customer’s share of regional revenue. First, aggregate order lines by customer and region:

WITH CustomerRevenue AS (
    SELECT c.region,
           c.customer_id,
           c.customer_name,
           SUM(ol.quantity * ol.unit_price) AS revenue
    FROM Customers AS c
    JOIN Orders AS o ON o.customer_id = c.customer_id
    JOIN OrderLines AS ol ON ol.order_id = o.order_id
    WHERE o.status = 'Completed'
    GROUP BY c.region, c.customer_id, c.customer_name
), Ranked AS (
    SELECT region, customer_id, customer_name, revenue,
           SUM(revenue) OVER (PARTITION BY region) AS regional_revenue,
           ROW_NUMBER() OVER (
               PARTITION BY region
               ORDER BY revenue DESC, customer_id
           ) AS position
    FROM CustomerRevenue
)
SELECT region, customer_id, customer_name, revenue,
       regional_revenue,
       revenue / NULLIF(regional_revenue, 0) * 100 AS regional_share_percent
FROM Ranked
WHERE position <= 2
ORDER BY region, position;

NULLIF(regional_revenue, 0) prevents division by zero by returning NULL when the denominator is zero. The secondary ordering by customer_id makes ROW_NUMBER() deterministic when revenues tie. If the business wants all customers tied for second place, RANK() should be used instead, accepting that more than two rows may be returned for a region.

10. Illustrative Case: Northstar Retail Analytics

Background

Northstar Retail operates stores and an online channel. Its data warehouse contains customers, orders, order lines, products, stores, and an employee hierarchy. Management wants a regional performance report.

Problem

A first report joined orders to both marketing events and product returns before aggregation. Revenue was overstated because one order could have several events and several return records. The report also showed only customers with orders, hiding inactive customers.

Analysis and approach

The analytics team adopted four controls:

1. aggregate order lines to order level before joining independent event data;
2. use a left join when measuring customers with no purchases;
3. use CTEs to separate revenue, customer segmentation, and ranking stages;
4. use window functions for regional totals and top-customer rankings.

A recursive CTE generated the management hierarchy so that sales could be attributed to each manager’s reporting tree. Conditional aggregation produced columns for online, store, and marketplace revenue.

Outcome

The revised report identified inactive customers accurately, removed revenue inflation, and made the top-customer definition explicit. Managers could distinguish “top two customers” from “all customers tied within the top two ranks.” The approach also improved auditability because each intermediate CTE represented a documented business rule.

Learning points

Complex SQL is not merely a technical exercise. Correct results depend on cardinality, grain, null handling, tie definitions, and the timing of aggregation. Every metric should have a declared grain—for example, revenue per order, customer, region, or month—before tables are joined.

11. Performance, Quality, and Governance

Advanced queries should be reviewed for:

• Cardinality: confirm whether each join is one-to-one, one-to-many, or many-to-many.
• Null semantics: distinguish zero, unknown, and not applicable values.
• Indexes and partitioning: index common join and filter columns where appropriate; use partition pruning for large time-partitioned tables.
• Execution plans: inspect scans, joins, sorts, spills, and estimated versus actual row counts.
• Predicate placement: filter early when it preserves intended semantics, but do not move predicates from ON to WHERE without checking outer-join behavior.
• Reproducibility: define tie-breaking columns and time zones explicitly.
• Security: apply least privilege, row-level security, masking, and parameterized inputs. Dynamic SQL should validate identifiers and never concatenate untrusted values.
• Privacy: avoid exposing unnecessary personal data and consider retention, consent, and applicable data-protection law.

SQL injection is particularly relevant when applications construct queries from user input. Values should be passed as parameters. Queries should also be tested against adversarial inputs, nulls, duplicate keys, missing relationships, and extreme data volumes.

Modern analytical platforms increasingly combine SQL with columnar storage, distributed execution, data catalogs, lineage tools, and AI-assisted query generation. AI can suggest joins or explain execution plans, but it cannot reliably determine undocumented business definitions. Human review remains essential for grain, authorization, sensitive attributes, and metric validity.

Best-Practice Checklist

1. State the business question and result grain before writing SQL.
2. Join tables through documented keys.
3. Estimate cardinality before aggregating.
4. Use UNION ALL only when duplicate preservation is intended.
5. Prefer NOT EXISTS over unsafe NOT IN patterns involving nullable columns.
6. Use CTEs to expose analytical stages, not as a substitute for performance testing.
7. Specify window ordering and frames explicitly.
8. Define how ties, missing values, and zero denominators are handled.
9. Test outer joins with unmatched rows.
10. Validate results against independent totals and representative edge cases.

Chapter Summary

Multi-table SQL enables analysts to integrate normalized data into meaningful business information. Inner joins select matching relationships, outer joins preserve unmatched entities, self-joins represent relationships within one table, and cross joins generate combinations. Set operations combine compatible result sets vertically, while subqueries and CTEs express nested or staged logic. Window functions calculate rankings, cumulative measures, moving statistics, and period comparisons without losing row detail. Conditional aggregation provides a portable approach to pivoting. Recursive CTEs traverse hierarchical structures.

Reliable advanced SQL depends on more than syntax. Analysts must control grain, understand cardinality, manage nulls and ties, prevent accidental row multiplication, protect sensitive data, and inspect execution behavior. These practices allow SQL to support transparent, reproducible, and decision-relevant business analytics.

Key Terms

Primary key: Column or combination of columns that uniquely identifies a row.

Foreign key: Column that references a key in another table.

Inner join: Join returning rows with matches on both sides.

Left join: Join preserving all rows from the left table.

Full outer join: Join preserving unmatched rows from both tables.

Cross join: Join producing combinations of rows from two tables.

Self-join: A table joined to itself.

Cardinality: The number and relationship pattern of rows produced by an operation.

Set operation: Operation combining compatible query results vertically.

Subquery: Query nested inside another query.

Correlated subquery: Subquery referring to values from its outer query.

CTE: Named temporary query defined with WITH.

Window function: Function calculating across related rows without collapsing them.

Partition: Subset of rows processed independently by a window function.

Window frame: Rows included around the current row in a window calculation.

Pivot: Transformation of row values into columns.

Recursive CTE: CTE that repeatedly references its own result.

Three-valued logic: SQL treatment of predicates as true, false, or unknown because of NULL.

Predicate: Condition used to filter or match rows.

Query grain: Level represented by each row in a query result.

Review Questions

Short Answer Questions

1. What is the principal difference between an inner join and a left join?
2. Why can joining two one-to-many tables inflate an aggregate total?
3. When is UNION ALL preferable to UNION?
4. What roles do PARTITION BY and ORDER BY play in a window function?
5. What are the anchor and recursive members of a recursive CTE?

Long Answer Questions

1. Explain how predicate placement in ON and WHERE can change the result of a left join. Include an example.
2. Compare scalar subqueries, multi-row subqueries, correlated subqueries, and CTEs.
3. Explain the differences among ROW_NUMBER(), RANK(), and DENSE_RANK() and identify an appropriate use for each.
4. Describe how conditional aggregation can be used to create a pivoted report and discuss its limitations.
5. Discuss the major correctness, performance, security, and privacy considerations when deploying advanced SQL in an organization.

Analytical / Application Questions

1. Design a query strategy for finding the top three products by revenue in each category, including how you would handle ties.
2. A report joins customers, orders, marketing events, and returns and produces revenue twice as high as the finance system. Diagnose the likely cause and propose a corrected query design.
3. Design a recursive query approach for an organizational hierarchy that detects or prevents cycles and reports each employee’s level and management path.

Multiple Choice Questions with Answers

Question 1

Which join preserves every row from the left table, even when no matching row exists in the right table?

A. Inner join

B. Left join

C. Cross join

D. Self-join

Correct Answer: B. Left join

Question 2

Which set operation retains duplicate rows?

A. UNION

B. INTERSECT

C. EXCEPT

D. UNION ALL

Correct Answer: D. UNION ALL

Question 3

Why is NOT EXISTS often safer than NOT IN when the subquery may return NULL?

A. NOT EXISTS always sorts the result

B. NOT EXISTS avoids problematic NULL comparison behavior

C. NOT EXISTS converts all values to zero

D. NOT EXISTS requires no join condition

Correct Answer: B. NOT EXISTS avoids problematic NULL comparison behavior

Question 4

What does PARTITION BY do in a window function?

A. Deletes duplicate rows

B. Creates independent groups for the calculation

C. Filters rows before the FROM clause

D. Converts rows into columns

Correct Answer: B. Creates independent groups for the calculation

Question 5

Which function assigns a unique sequential number to rows within each window partition?

A. RANK()

B. DENSE_RANK()

C. ROW_NUMBER()

D. COUNT()

Correct Answer: C. ROW_NUMBER()

Question 6

Which clause is most directly associated with transforming category values into separate report columns through portable SQL?

A. CASE inside aggregate expressions

B. ORDER BY inside a join

C. EXCEPT inside GROUP BY

D. WHERE inside UNION

Correct Answer: A. CASE inside aggregate expressions

Question 7

What are the two essential parts of a recursive CTE?

A. A primary key and a foreign key

B. An anchor member and a recursive member

C. A pivot and an unpivot

D. A WHERE clause and a HAVING clause

Correct Answer: B. An anchor member and a recursive member

Question 8

If a customer has three orders and each order has four order-line rows, how many order-line rows match that customer in a direct customer-to-order-line relationship?

A. 3

B. 4

C. 7

D. 12

Correct Answer: D. 12

Question 9

Which function gives tied rows the same rank while leaving gaps after the tie?

A. ROW_NUMBER()

B. RANK()

C. DENSE_RANK()

D. NTILE()

Correct Answer: B. RANK()

Question 10

In the expression revenue / NULLIF(regional_revenue, 0), what is the purpose of NULLIF?

A. It converts revenue to an integer

B. It prevents division by zero by returning NULL when the denominator is zero

C. It removes all NULL rows from the result

D. It ranks regions by revenue

Correct Answer: B. It prevents division by zero by returning NULL when the denominator is zero