Comprehensive Guide to Multi-Table Queries and Joins in SQL

Multi-table queries, also known as join queries, involve retrieving data from two or more related tables.

Prerequisite: tables must have a relationship (one-to-one, one-to-many) via a common column, which may or may not be defined as a foreign key, e.g., employees and departments linked by department_id .

01 Case: Incorrect Cartesian Product

Example of a wrong implementation that matches every employee with every department, producing 2,889 rows:

SELECT employee_id, department_name FROM employees, departments;

Analysis shows the Cartesian product problem: employee count (107) × department count (27) = 2,889.

The error is called a Cartesian product (cross join).

02 Cartesian Product (Cross Join)

Cartesian product combines every row of one set with every row of another set; the number of results equals the product of the row counts.

In SQL92 and SQL99 this is expressed with CROSS JOIN , which joins tables even without a relationship.

Equivalent erroneous query:

SELECT employee_id, department_name FROM employees CROSS JOIN departments;

Cartesian product occurs when join conditions are omitted, invalid, or when all rows are combined.

To avoid it, add valid join conditions in the WHERE clause, e.g.:

SELECT last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id;

Note: prefix column names with table aliases when columns share the same name; using aliases requires consistent use throughout the query.

Multiple join conditions are combined with AND .

Example with three tables:

SELECT e.employee_id, e.last_name, d.department_name, l.city, e.department_id, l.location_id FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

When n tables are joined, at least n‑1 join conditions are needed.

03 Types of Joins

1. Equality vs. non‑equality joins.

2. Self‑joins vs. non‑self joins (joining a table to itself using aliases).

3. Inner join vs. outer join.

Inner join returns rows with matching values in both tables; outer join returns all rows from one side plus matching rows from the other, filling non‑matching columns with NULL.

Left outer join example:

SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

Right outer join example:

SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;

Full outer join (SQL99) combines left and right outer joins; MySQL lacks native support, so it can be simulated with LEFT JOIN UNION RIGHT JOIN .

04 UNION

UNION merges results of multiple SELECT statements with identical column counts and compatible data types; UNION ALL keeps duplicates and is faster.

Syntax:

SELECT column FROM table1 UNION [ALL] SELECT column FROM table2;

05 Seven SQL JOIN Implementations

Examples of inner join, left join, right join, left‑anti join (A − A∩B), right‑anti join, full join using UNION ALL, and full outer join via UNION.

06 SQL99 New Features

Natural join automatically joins tables on columns with the same name; equivalent to an equality join on those columns.

USING clause specifies the common column(s) for an equality join, simplifying ON syntax.

Join conditions can be expressed with WHERE , ON , or USING .

Be cautious about joining too many tables, as it can degrade performance and hit DBMS limits.

Appendix

SQL standards overview (SQL‑86, SQL‑89, SQL‑92, SQL‑99, SQL‑2003, etc.) and their impact on join syntax.

Practice Exercises

Part 1 and Part 2 provide a series of query tasks covering basic selects, joins, outer joins, UNION, and emulating FULL JOIN in MySQL.