SQL JOINs

Why it matters

Many data defects live between tables: an order pointing to a deleted customer, a payment without its order, an import that wrote ids from another system. Each table looks correct on its own; only a join shows the broken references. Undetected, they make screens show fewer items than exist and make reports disagree.

Joins are also a trap for the tester. An inner join hides unmatched rows without any error, and a wrong join condition returns plausible but wrong rows. The right join type and a row count you predicted turn a query into evidence instead of a false pass.

How a join matches rows

You name both tables in FROM … JOIN and write the join condition after ON, usually a relationship like those in the lesson on keys and relationships: the foreign key orders.customer_id equals the primary key it references, here customers.customer_id. The result holds every pair of rows for which the condition is true.

In the tables below, order 104 references customer 9, who does not exist: the kind of broken reference a tester looks for. c and o are aliases, short names for the tables.

-- customers                    orders
-- customer_id  full_name       order_id  customer_id  total
-- 1            Layla           101       1            250.00
-- 2            Omar            102       1             40.00
-- 3            Priya           103       2            120.00
--                              104       9             75.00

SELECT c.customer_id, c.full_name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;
-- 3 rows: customer 1 with orders 101 and 102, customer 2 with order 103

INNER, LEFT, RIGHT and FULL joins

All four types return the matched pairs and differ only in unmatched rows. Customers, written first, is the left table; orders is the right table.

• INNER JOIN, or just JOIN: matched pairs only, 3 rows. Customer 3, who has no orders, and order 104, whose customer is missing, disappear without any warning.
• LEFT JOIN (LEFT OUTER JOIN): every row of the left table, 4 rows. Customer 3 is kept, with NULL in the order columns.
• RIGHT JOIN (RIGHT OUTER JOIN): every row of the right table, 4 rows. Order 104 is kept, with NULL in the customer columns.
• FULL JOIN (FULL OUTER JOIN): every row of both tables, 5 rows. PostgreSQL, SQL Server and Oracle Database support it; MySQL does not, and SQLite supports RIGHT and FULL joins only from version 3.39.0.

SELECT c.customer_id, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

-- customer_id  order_id  total
-- 1            101       250.00
-- 1            102        40.00
-- 2            103       120.00
-- 3            NULL      NULL     <- kept by LEFT JOIN, dropped by INNER JOIN

Try it: SQL Formatter Try it: SQL Query Validator

Writing the join condition

Most wrong join results come from the ON clause, and a wrong condition still returns rows that look reasonable, so compare every row count with the number you expect.

In an outer join, the place of an extra filter matters. In ON it only decides what counts as a match, so unmatched left rows stay. In WHERE it runs after the join, removes the NULL-filled rows and silently turns the left join into an inner join, as the two queries below show. A test for IS NULL is the exception: it keeps exactly the NULL-filled rows, which is how the orphan check in the next section works.

• Join on the columns the relationship uses: o.customer_id = c.customer_id. The condition o.order_id = c.customer_id also runs, but pairs unrelated rows wherever the two numbers happen to be equal.
• Use every column of a composite key: if customer ids are unique only within a tenant (one client organisation in a shared, multi-tenant system), join on both tenant_id and customer_id; otherwise an order is also paired with other tenants’ customers who have the same number.
• Expect repetition in one-to-many joins: customer 1 appears twice because it has two orders, so counting the rows counts orders, not customers.
• Leaving the condition out pairs every row with every row, a Cartesian product, which CROSS JOIN returns on purpose: 3 customers and 4 orders give 12 rows. It happens with FROM customers, orders (tables separated by a comma) when no WHERE links them, and in MySQL with a JOIN that has no ON; PostgreSQL rejects such a JOIN.
• NULL never equals anything, not even another NULL, so a row whose join column is NULL never matches: an inner join drops it, a left join keeps it.

-- Every customer, with their orders over 100.00 if any: 3 rows
SELECT c.customer_id, o.order_id, o.total
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.total > 100.00;
-- 1 | 101 | 250.00     2 | 103 | 120.00     3 | NULL | NULL

-- The same filter in WHERE also removes customer 3: 2 rows
SELECT c.customer_id, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.total > 100.00;
-- 1 | 101 | 250.00     2 | 103 | 120.00

Finding orphan rows with LEFT JOIN … IS NULL

An orphan row is a child row whose foreign key points to a parent that does not exist, like order 104. Start from the child table, left join the parent and keep only the rows where no parent matched. A correct database returns zero rows; every returned row is evidence for a bug report. Reversed, the same shape finds parents missing required children, such as an order with no items.

Test IS NULL on the parent’s primary key, which is never NULL after a match; a nullable column such as an optional phone number would also report the orders of customers without a phone. Children whose foreign key is itself NULL appear too: a defect if the column is required, otherwise exclude them with AND o.customer_id IS NOT NULL.

The second query in the block below writes the same check with NOT EXISTS. The query in brackets, a subquery, is checked for each order; NOT EXISTS is true when it finds no row. SELECT 1 only says that the subquery’s columns do not matter, only whether a row exists.

An enforced foreign key constraint prevents orphans, yet they still appear in practice:

• The constraint was never declared, which is common in legacy schemas.
• Constraints were disabled for a bulk import or a migration.
• SQLite enforces foreign keys only when PRAGMA foreign_keys = ON is set for the connection.
• Parent and child live in the databases of two different services.

-- Orders whose customer does not exist (expected: 0 rows)
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- returns order 104 with customer_id 9: an orphan

-- The same check written with NOT EXISTS
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);

Try it: SQL INSERT Generator Try it: SQL Dataset Generator

Worked example: orders missing from the order history

After a migration from an old shop system, a support ticket says some customers do not see all their orders on the "My orders" page, which joins orders to customers with an inner join. The tester suspects orders that point to customers who do not exist.

• Expected: every migrated order belongs to an existing customer, so the orphan check returns 0 rows.
• Actual: 13 rows, all with a customer_id above 900000, the old system’s numbering. The migration copied old customer numbers instead of mapping them to the new ids, so these orders belong to no existing customer: the page’s inner join can never pair them with the signed-in customer, and nothing raises an error.
• Report: the query, the 13 order ids, the expected 0 rows, the suspected cause, and the question whether a foreign key constraint should be added.
• Confirmation testing after the fix: the same query returns 0 rows, and an affected customer sees the missing orders on the page.

-- Orphan check after the migration (expected: 0 rows)
SELECT o.order_id, o.customer_id, o.created_at
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;

Frequently asked questions

References

• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupLexical structure of SQL in PostgreSQL: identifiers, quoting, string constants and escaping.
• SQL As Understood By SQLiteSQLiteThe SQL grammar accepted by SQLite, statement by statement, with railroad diagrams.
• Constraints — PostgreSQL DocumentationPostgreSQL Global Development GroupCheck, not-null, unique, primary-key, foreign-key and exclusion constraints — the rules a database enforces and a tester verifies.