Joins and Set Operations

Introduction

A relational database stores information in related tables rather than one large duplicated table. A customer’s name may be stored in customers, while that customer’s orders are stored in orders. SQL joins combine rows from these tables according to a logical relationship. Set operations combine the results of compatible queries.

Joins are central to application development, reporting, analytics, and data integration. However, an incorrect join can silently duplicate rows, omit important records, or produce misleading business conclusions. This chapter develops the theory and practice of inner, outer, cross, and self joins, then examines UNION, INTERSECT, and EXCEPT.

Learning Objectives

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

• Explain how join predicates determine which rows are combined.
• Write inner, left, right, full outer, cross, and self joins.
• Distinguish one-to-one, one-to-many, and many-to-many relationships.
• Diagnose unexpected row multiplication and missing rows.
• Use UNION, INTERSECT, and EXCEPT when combining compatible result sets.
• Select an appropriate operation for practical database tasks.

1. Relational Foundations

A relation is conceptually a set of tuples (rows) described by attributes (columns). In practical SQL systems, tables may contain duplicate rows unless uniqueness is enforced, so SQL is often described as operating on multisets or bags. This distinction matters: UNION removes duplicates by default, whereas UNION ALL preserves them.

Consider the following simplified schema:

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    manager_id INTEGER,
    salary DECIMAL(10,2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

A primary key identifies a row uniquely. A foreign key refers to a candidate key in another table. A foreign key may be nullable unless declared NOT NULL; therefore, the schema and business rules must be examined before predicting join results.

Join terminology

• Join key: column or expression used to match rows.
• Join predicate: Boolean condition defining a match.
• Matching row: row satisfying the predicate.
• Preserved table: table whose rows remain in an outer join even without a match.
• Cardinality: number of rows produced or relationship multiplicity.
• Equijoin: join using equality, such as a.id = b.id.
• Non-equijoin: join using another comparison, such as < or BETWEEN.

2. Inner Joins

An inner join returns only combinations satisfying its join condition. The explicit JOIN ... ON form is preferred because it separates relationship logic from filtering logic.

SELECT c.customer_id, c.customer_name, o.order_id, o.total_amount
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id;

A customer with no orders is absent from this result. An order whose customer_id does not match a customer is also absent, although a valid foreign key normally prevents the latter.

The same relationship can be written using USING when both columns have the same name:

SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
JOIN orders AS o USING (customer_id);

USING is not available in every database system, so ON is more portable.

Filtering in ON and WHERE

For an inner join, these forms often produce the same result:

SELECT c.customer_name, o.order_id
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.total_amount >= 100;

SELECT c.customer_name, o.order_id
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.total_amount >= 100;

The distinction becomes important with outer joins, because a WHERE condition applied to a null-extended row can remove that row.

3. Outer Joins

Outer joins preserve rows that do not find a match. Missing columns from the other table are represented by NULL.

Left outer join

A left join preserves every row from the table on the left:

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 is suitable for finding customers with and without 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;

To count orders accurately, use a nullable-side column such as o.order_id, not COUNT(*):

SELECT c.customer_id, c.customer_name,
       COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

COUNT(o.order_id) counts only non-null order identifiers, whereas COUNT(*) counts the preserved customer row even when no order exists.

Right outer join

A right join preserves every row from the right table:

SELECT d.department_name, e.employee_name
FROM employees AS e
RIGHT JOIN departments AS d
    ON e.department_id = d.department_id;

A right join can usually be rewritten as a left join by reversing table order. Many teams prefer left joins because they are easier to read consistently.

Full outer join

A full join preserves unmatched rows from both tables:

SELECT d.department_name, e.employee_name
FROM departments AS d
FULL OUTER JOIN employees AS e
    ON e.department_id = d.department_id;

It is useful for reconciliation, such as comparing two systems. Some database products do not support FULL OUTER JOIN directly; an equivalent design may require a union of a left join and a right-only join, with care to avoid duplicates.

Outer-join predicate placement

Suppose the objective is to list every customer and show only orders of at least 100 monetary units. The condition belongs in ON:

SELECT c.customer_name, o.order_id, o.total_amount
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.total_amount >= 100;

If o.total_amount >= 100 is placed in WHERE, customers with no qualifying order are removed. Thus, the first query preserves the left-table population; the second behaves like an inner join for that condition.

4. Cross Joins

A cross join produces the Cartesian product: every row of one input is paired with every row of the other.

SELECT d.department_name, s.shift_name
FROM departments AS d
CROSS JOIN shifts AS s;

If the first input has m rows and the second has n rows, the result has m × n row combinations, assuming ordinary bag semantics and no later filter. For 6 departments and 3 shifts:

A cross join is appropriate for generating all possible combinations, such as products and sizes, dates and stores, or departments and scheduled shifts. It is dangerous when caused accidentally by omitting a join predicate.

5. Self Joins

A self join joins a table to itself using aliases. It is useful for hierarchical or comparative relationships.

The displayed employees schema includes both department_id and manager_id, so the following query is valid:

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id;

The employee alias e represents the subordinate row, while m represents the manager row. The left join keeps top-level employees whose manager_id is null.

A self join can also compare employees in the same department:

SELECT e1.employee_name AS employee_1,
       e2.employee_name AS employee_2,
       e1.department_id
FROM employees AS e1
JOIN employees AS e2
  ON e1.department_id = e2.department_id
 AND e1.employee_id < e2.employee_id;

The inequality prevents pairing an employee with themself and ensures that each unordered pair appears once rather than twice. This query is valid because department_id is explicitly present in the schema.

For deep organizational hierarchies, repeated self joins become impractical. Recursive common table expressions are generally more suitable:

WITH RECURSIVE org AS (
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id, org.level + 1
    FROM employees AS e
    JOIN org ON e.manager_id = org.employee_id
)
SELECT * FROM org;

Syntax for recursive queries varies across database systems.

6. Join Cardinality and Row Multiplication

Join cardinality describes how many output rows can result from matching input rows. The relationship type provides useful bounds, but the exact result depends on data and predicates.

For a primary-key-to-foreign-key join, each child row matches at most one parent row. Therefore, joining 450 orders to customers by orders.customer_id = customers.customer_id, when customer_id is a primary key in customers, produces at most 450 matching rows. It produces exactly 450 if every order has a matching customer, as required by a valid non-null foreign key. It cannot exceed 450 under these stated conditions.

The general one-to-many pattern is:

parent row → zero, one, or many child rows

A customer with 4 orders contributes 4 rows to a customer–order inner join. A customer with no order contributes no row to that inner join but contributes one null-extended row to a left join.

Many-to-many multiplication

Suppose 3 students are enrolled in 4 modules and the enrollment table contains 12 distinct student-module pairs. Joining students to enrollments and then modules produces 12 enrollment rows. If a query joins two independent one-to-many child tables directly, multiplication can occur. For example, a customer with 3 orders and 2 support tickets may produce 3 × 2 = 6 rows when both child tables are joined to the customer in one query.

A safer design is to aggregate each child table first:

SELECT c.customer_id,
       COALESCE(o.order_count, 0) AS order_count,
       COALESCE(t.ticket_count, 0) AS ticket_count
FROM customers AS c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
) AS o ON o.customer_id = c.customer_id
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS ticket_count
    FROM support_tickets
    GROUP BY customer_id
) AS t ON t.customer_id = c.customer_id;

Diagnostic techniques

1. Identify the intended grain: one row per customer, order, or customer-order pair?
2. Check uniqueness of join columns.
3. Count rows before and after each join.
4. Inspect duplicate keys with GROUP BY ... HAVING COUNT(*) > 1.
5. Use COUNT(DISTINCT key) only when deduplication reflects the business meaning; it should not conceal an incorrect join.
6. Check execution plans and indexes for performance, while remembering that an index does not correct logical duplication.

7. Set Operations

Set operations combine complete query results vertically. The participating queries must have the same number of columns, in compatible positions and data types. Column names in the final result normally come from the first query.

UNION

UNION concatenates results and removes duplicate rows:

SELECT email FROM current_customers
UNION
SELECT email FROM archived_customers;

If the first result has 80 distinct emails, the second has 50, and 20 appear in both, the result has:

distinct rows, assuming each input is already duplicate-free.

UNION ALL retains duplicates and usually requires less work:

SELECT email FROM current_customers
UNION ALL
SELECT email FROM archived_customers;

Use UNION ALL when duplicate occurrences are meaningful or when sources are known to be mutually exclusive.

INTERSECT

INTERSECT returns rows present in both results:

SELECT customer_id FROM customers_2024
INTERSECT
SELECT customer_id FROM customers_2025;

It answers a membership question: which customers occur in both populations? Duplicate handling follows the database’s set-operation rules; standard INTERSECT returns distinct rows. Some systems also support INTERSECT ALL.

EXCEPT

EXCEPT returns rows in the first result that are absent from the second:

SELECT customer_id FROM customers_2024
EXCEPT
SELECT customer_id FROM customers_2025;

The order matters. Reversing the queries asks for customers in 2025 but not 2024. Oracle commonly uses the equivalent keyword MINUS rather than EXCEPT.

Set operations versus joins

A join combines columns horizontally according to relationships. A set operation combines rows vertically according to result compatibility.

Parentheses should be used when mixing set operators or applying ordering and limiting. An ORDER BY generally applies to the final combined result:

(
    SELECT customer_id FROM customers_2024
    UNION
    SELECT customer_id FROM customers_2025
)
ORDER BY customer_id;

8. Worked Application: Retail Data Reconciliation

Background

This illustrative case concerns a retailer with a live customer table and a legacy customer export. The data team must identify active customers who also appear in the legacy file, find legacy-only customers, and report every active customer with any orders.

Problem

The tables are:

• customers(customer_id, customer_name, email)
• orders(order_id, customer_id, order_date, total_amount)
• legacy_customers(customer_id, email)

The team initially writes a single query joining orders and legacy customers directly to customers. The output contains repeated customers because customers may have multiple orders and multiple legacy records.

Analysis

The tasks have different relational meanings:

1. Customer details plus order details require a join.
2. Membership in both customer populations requires INTERSECT.
3. Legacy-only identifiers require EXCEPT.
4. A customer-level report should aggregate orders before joining if one row per customer is required.

Solution

Common active and legacy identifiers:

SELECT customer_id FROM customers
INTERSECT
SELECT customer_id FROM legacy_customers;

Legacy-only identifiers:

SELECT customer_id FROM legacy_customers
EXCEPT
SELECT customer_id FROM customers;

One row per active customer with order totals:

SELECT c.customer_id,
       c.customer_name,
       COALESCE(o.order_count, 0) AS order_count,
       COALESCE(o.total_spend, 0.00) AS total_spend
FROM customers AS c
LEFT JOIN (
    SELECT customer_id,
           COUNT(*) AS order_count,
           SUM(total_amount) AS total_spend
    FROM orders
    GROUP BY customer_id
) AS o ON o.customer_id = c.customer_id;

Outcome and learning points

The revised design preserves customers with no orders, avoids order-level duplication in the customer report, and uses set operations for population comparison. The central lesson is to define the required grain before choosing an operation.

9. Performance, Integrity, and Governance

Join performance depends on table size, indexes, statistics, predicates, data distribution, and the optimizer. Indexes on primary and foreign keys commonly support joins, but indexing every column can increase storage and update cost. Select only required columns, filter early where logically safe, and inspect execution plans for large queries.

Constraints improve correctness. Primary keys prevent duplicate identifiers; foreign keys protect referential integrity; NOT NULL expresses required relationships; and unique constraints protect alternate identifiers such as email addresses. Without constraints, assumptions about cardinality may be false.

Null values require special care. NULL = NULL is not true in SQL’s three-valued logic; it evaluates to unknown. Use IS NULL or IS NOT NULL. Do not replace nulls with arbitrary values merely to force a match, because that can create false relationships.

Access control also matters. A join may combine personally identifiable information with transactions. Apply least privilege, mask sensitive columns, audit access, and follow applicable privacy and retention requirements. Set operations used for reconciliation should be documented so that analysts can explain why records were included or excluded.

Modern query engines may use hash joins, merge joins, or nested-loop joins. These are implementation strategies chosen by the optimizer; SQL authors should focus first on correct semantics and accurate constraints. AI-assisted SQL tools can suggest joins, but generated queries require human review of keys, null behavior, cardinality, privacy, and business meaning.

Best Practices

• State the intended grain before writing the query.
• Use explicit JOIN ... ON syntax.
• Qualify columns with aliases.
• Verify that join keys have the expected uniqueness.
• Use left joins deliberately when unmatched left rows matter.
• Put outer-join-preserving filters in ON when appropriate.
• Use COUNT(column) rather than COUNT(*) when counting matched child rows.
• Prefer UNION ALL when deduplication is neither required nor meaningful.
• Use parentheses when combining set operations.
• Test empty, duplicate, null, and unmatched cases.
• Validate output counts against known constraints and business expectations.

Chapter Summary

Joins combine columns from related tables, while set operations combine compatible query results. Inner joins retain matching combinations; outer joins preserve selected unmatched rows with null-extended columns; cross joins generate Cartesian products; and self joins use aliases to compare or navigate rows within one table. Join cardinality follows key constraints and relationship multiplicity. A primary-key-to-foreign-key join from 450 orders to unique customers cannot produce more than 450 rows and produces 450 when all orders have valid customers. Independent one-to-many joins can multiply rows, so aggregation or carefully designed subqueries may be necessary. UNION removes duplicates, UNION ALL preserves them, INTERSECT finds common rows, and EXCEPT finds rows present only in the first query. Correct SQL depends on keys, null semantics, intended grain, integrity constraints, and responsible handling of sensitive data.

Key Terms

Join: Operation that combines rows from tables using a predicate.

Join predicate: Boolean condition that determines whether rows match.

Inner join: Join returning only matching row combinations.

Outer join: Join preserving unmatched rows from one or both inputs.

Left join: Outer join preserving every row from the left input.

Right join: Outer join preserving every row from the right input.

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

Cross join: Cartesian product of two inputs.

Self join: A table joined to itself through aliases.

Join key: Column or expression used to relate rows.

Cardinality: Number of rows or multiplicity of a relationship.

One-to-many: Relationship in which one parent can have many children.

Cartesian product: All possible pairings of two row sets.

Null extension: Filling missing outer-join columns with NULL.

UNION: Combines compatible results and removes duplicate rows.

UNION ALL: Combines compatible results while retaining duplicates.

INTERSECT: Returns rows common to both results.

EXCEPT: Returns rows in the first result but not the second.

Grain: The real-world entity represented by one output row.

Referential integrity: Consistency between foreign-key values and referenced keys.

Review Questions

Short Answer Questions

1. What is the difference between an inner join and a left outer join?
2. Why can an accidental missing join predicate create a Cartesian product?
3. What role do aliases play in a self join?
4. Why might COUNT(o.order_id) be preferable to COUNT(*) in a left join?
5. State the compatibility requirements for queries combined with a set operation.

Long Answer Questions

1. Explain one-to-one, one-to-many, and many-to-many relationships, using SQL examples.
2. Compare UNION, UNION ALL, INTERSECT, and EXCEPT, including duplicate behavior and typical uses.
3. Explain how placing a filter in ON rather than WHERE affects a left outer join.
4. Discuss causes of join row multiplication and describe a systematic debugging approach.
5. Explain how primary keys, foreign keys, nullability, and unique constraints affect join-cardinality reasoning.

Analytical / Application Questions

1. A report should contain one row per customer, including customers without orders, with order count and total spending. Design a query strategy that avoids duplication and justify each operation.
2. A table contains 600 orders, and every order has a non-null foreign key referencing a unique customer identifier. What is the maximum possible number of rows in an inner order-customer join? Explain the assumptions.
3. A data auditor must find identifiers appearing in the current customer population but not in a legacy extract, then investigate records appearing in both. Choose suitable set operations and explain why a join is not the most direct first choice.

Multiple Choice Questions with Answers

Question 1

Which query preserves every customer, including those with no orders?

A. SELECT * FROM customers c INNER JOIN orders o ON o.customer_id = c.customer_id

B. SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id

C. SELECT * FROM customers c CROSS JOIN orders o

D. SELECT * FROM customers c RIGHT JOIN orders o ON o.customer_id = c.customer_id

Correct Answer: B. SELECT * FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id

Question 2

Under a valid non-null foreign key from 450 orders to unique customer IDs, what is the maximum number of rows in the inner order-customer join?

A. 225

B. 450

C. 900

D. The number of customers squared

Correct Answer: B. 450

Question 3

What does a cross join produce?

A. Only unmatched rows

B. Only rows with equal keys

C. Every possible pairing of rows from the two inputs

D. Only duplicate-free rows

Correct Answer: C. Every possible pairing of rows from the two inputs

Question 4

Why is the condition e1.employee_id < e2.employee_id useful in a same-department employee self join?

A. It removes all employees without managers

B. It prevents self-pairs and reverse duplicate pairs

C. It converts the join into a cross join

D. It requires employee IDs to be foreign keys

Correct Answer: B. It prevents self-pairs and reverse duplicate pairs

Question 5

Which operation returns rows present in both query results?

A. UNION

B. EXCEPT

C. INTERSECT

D. CROSS JOIN

Correct Answer: C. INTERSECT

Question 6

Which operation preserves duplicate rows when combining compatible query results?

A. UNION

B. UNION ALL

C. INTERSECT

D. EXCEPT

Correct Answer: B. UNION ALL

Question 7

A customer has 3 orders and 2 support tickets. Joining both child tables directly to that customer can produce how many combinations for that customer?

A. 2

B. 3

C. 5

D. 6

Correct Answer: D. 6

Question 8

Which clause is generally appropriate when a left join must preserve customers while matching only orders of at least 100?

A. Put the amount condition in the ON clause

B. Put the amount condition only in SELECT

C. Replace the join with a cross join

D. Put the condition in a GROUP BY clause

Correct Answer: A. Put the amount condition in the ON clause

Question 9

What does EXCEPT return in a query A EXCEPT query B?

A. Rows common to A and B

B. All rows from A and B including duplicates

C. Rows in A that are absent from B

D. Rows in B that are absent from A

Correct Answer: C. Rows in A that are absent from B

Question 10

Which statement about set operations is correct?

A. The queries may return different numbers of columns

B. Corresponding columns should have compatible data types

C. Set operations always combine columns horizontally

D. EXCEPT is unaffected by operand order

Correct Answer: B. Corresponding columns should have compatible data types