SQL Joins and Table Relationships

A customer's address belongs with the customer. An order's date belongs with the order. Keeping these facts in separate tables reduces repetition, but a report often needs facts from both. A join combines their rows inside a query.

Read the keys

A primary key identifies one row. It may be one column or several columns together. A foreign key refers to a primary key or another unique key, either in another table or in the same table. In the store database, orders.customer_id points to customers.customer_id.

Orders 101 and 102 both refer to customer 1, Ana. Order 103 refers to Ben. Order 104 is a guest checkout: its customer key is NULL, so it refers to no customer. The order number identifies the order; it does not identify the customer. Joining order_id to customer_id would compare unrelated identifiers.

Name the relationship

The customer–order relationship is one-to-many. A customer can have no orders, one order, or several. That does not mean the customer table stores an order ID for every purchase. Each order stores its own customer reference.

For students and courses, neither side can hold just one reference to describe all the links. An enrollments row records one student–course pair. Several enrollment rows can refer to the same student, and several can refer to the same course.

Trace a route before writing SQL

To list products bought by each customer, follow customers → orders → order_items → products. The links use customer_id, then order_id, then product_id. Each link needs its own condition. An order line supplies the missing connection between an order and a product.

Read the schema's meaning, not just the spelling of columns. employees.manager_id can refer to employees.emp_id, even though the names differ. Conversely, customers.name and products.name name different kinds of things and should not be matched just because both are called name.

A declared foreign key is a useful guide, but SQL can also join tables without a declared constraint. A price can match a price band, and two employees can match because they share a manager. The condition must express the relationship needed for the question.

A join builds new rows by combining input rows. Start with the matching rule, then ask how many partners each row has. The answer controls both which rows appear and how often their values repeat.

Think in pairs

For an inner join, imagine every customer beside every order. Keep a pair only when o.customer_id = c.customer_id is true. Order 101 pairs with Ana; it does not pair with Ben, Chloe, or Dev. Order 102 also pairs with Ana. One customer can therefore contribute several result rows.

The condition can be true, false, or unknown. An equality involving NULL is unknown, so guest order 104 finds no customer match. An outer join also preserves unmatched rows from its chosen side and fills the missing partner's columns with NULL.

This pair-by-pair picture explains joins better than overlapping circles. A join does not merely collect shared items: it constructs combined rows, and repeated keys can create more output rows than either input contains. If two rows on one side and three on the other share a key, that key gives six matching pairs.

Choose columns independently of matches

The store's customer table has three columns and its order table has four. With JOIN ... ON, SELECT * returns all seven, including both copies of customer_id. You can instead select only the fields the report needs:

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

The result is (Ana, 101, 22.00), (Ana, 102, 25.50), and (Ben, 103, 60.00). The key columns do their matching work without having to appear in the selected output. Selecting fewer columns changes the width of the result, not which input pairs match.

Give tables short, clear names

In FROM customers c, c is a table alias. Refer to that table's columns as c.name and c.customer_id. The expression c.name is a qualified column name: the part before the period says which table role supplies the value.

Both joined tables contain customer_id. Writing only SELECT customer_id with the ON form leaves the database unable to choose between them and causes an ambiguous-column error. Write c.customer_id or o.customer_id according to the value you need. Qualifying other selected columns also makes their origin easy to see.

AS before a table alias is optional in PostgreSQL, SQLite, MySQL, and SQL Server. Oracle does not accept it, so FROM customers c is a useful shared form. After giving a table an alias, use that alias consistently.

Read the query's logical order

FROM and its joins build the combined rows. WHERE filters those rows. SELECT chooses the output columns, and ORDER BY sorts the result. A filter can use a joined column even when that column is not selected. For example, WHERE c.city = 'Denver' can filter a report that displays only names and order totals.

Without ORDER BY, the database promises no particular row order. A query that displays Ana first today may display another row first later; sorting is a separate instruction from matching.

Use an inner join when a result row needs a match in both tables. JOIN and INNER JOIN mean the same thing. Neither one preserves a row that finds no partner.

Build the query in three parts

First name the tables and their roles. Then state the relationship in ON. Finally choose the columns to display.

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

Read the condition as “the order's customer reference equals this customer's identifier.” For order 101, the reference is 1, so it pairs with Ana. Order 102 also has reference 1, and order 103 has reference 2. The complete result is:

Ana appears twice because she has two matching orders. Chloe and Dev have none, so they do not appear. Guest order 104 has a NULL customer reference, which satisfies no equality with a customer key.

Filter and sort the combined rows

To show only Denver customers, place WHERE c.city = 'Denver' after the join and before ORDER BY. The result becomes (Ana, 101, 22.00) and (Ana, 102, 25.50). Chloe lives in Denver too, but the inner join never produced a row for her.

For an inner join, putting this filter in ON gives the same pairs. A readable convention is to keep table-matching conditions in ON and report filters in WHERE. This equivalence does not extend to outer joins.

You can sort by a joined column that is absent from the selected output. You can also omit the join keys from the output: the query above matches on customer_id while displaying a name, an order number, and a total. A column's role in matching is separate from its role in the report.

Match every part of a key

Some records are identified by a pair of columns. A shipment line that refers to an order line may need both the order and product identifiers:

SELECT s.order_id, s.product_id, oi.quantity
FROM shipment_lines s
JOIN order_items oi
  ON s.order_id = oi.order_id
 AND s.product_id = oi.product_id;

Both comparisons must be true for a match. Matching only order_id would attach each shipment line to every product line in that order. The query could run successfully while describing the wrong product quantities.

Change the order without changing the meaning

Writing FROM orders o JOIN customers c ON c.customer_id = o.customer_id gives the same customer–order pairs. Reversing the two sides of = also preserves an equality. SELECT * may arrange columns differently after a table swap, and row order remains unspecified unless sorted.

A much more serious change is ON o.order_id = c.customer_id. Both columns contain numbers, so this is legal SQL, but they identify different things. In the store data it returns zero rows: order IDs 101–104 never equal customer IDs 1–4. Check the schema relationship even when the database reports no syntax error.

ON makes a condition explicit, but existing SQL may use shorter or older forms. Read each form by asking which pairs it keeps. Some forms also change how shared columns appear in the output.

USING names the shared key

When the key columns have the same name, this query uses a shorthand equality:

SELECT c.name, o.order_id
FROM customers c
JOIN orders o USING (customer_id)
ORDER BY o.order_id;

It returns (Ana, 101), (Ana, 102), and (Ben, 103). USING (customer_id) means to compare the two customer_id columns. With SELECT *, it returns six columns, showing the shared key once; JOIN ... ON includes both input key columns.

For a composite key, list both names: USING (order_id, product_id). Each listed column must exist under that name in both inputs.

PostgreSQL, SQLite, MySQL, and Oracle support USING; SQL Server does not. In Oracle, a column named in USING must be unqualified when referenced, so write SELECT customer_id, not SELECT c.customer_id. Other columns can still use their table aliases.

NATURAL JOIN chooses columns for you

A NATURAL JOIN compares every column name shared by the inputs, combines those equality tests with AND, and shows the shared columns once. It is supported by PostgreSQL, SQLite, MySQL, and Oracle, but not SQL Server.

That automatic choice can silently change a query. Customers and orders currently share only customer_id. Adding a city column to orders makes a natural join require both customer ID and city to match. A delivery city different from the customer's city can then remove a formerly matching order.

Customers and products both have name, but one names a person and the other names a product. Their natural join would compare those unrelated names and returns no rows in the store data. With no shared column names, a natural join instead returns every combination. Use ON or an intentional USING list to keep the matching rule visible.

Read older comma joins

SELECT c.name, o.order_id
FROM customers c, orders o
WHERE o.customer_id = c.customer_id;

This returns the same three pairs as the inner join. The condition now lives in WHERE. Forget it and all four customers pair with all four orders, producing 16 rows. JOIN ... ON keeps each table beside its own condition, which makes longer queries easier to check.

Match a value to a range

An equijoin uses equality. A non-equijoin uses another comparison, such as a range test:

SELECT p.name, b.band
FROM products p
JOIN price_bands b
  ON p.price BETWEEN b.min_price AND b.max_price;

The bands are Budget 0.00–4.99, Standard 5.00–19.99, and Premium 20.00–999.99. Mug at 8.00 and Stapler at 12.00 match Standard; Pen at 1.50 and Notebook at 4.25 match Budget. Premium has no matching product and contributes no row.

BETWEEN includes both endpoints. A new Gift band from 8.00 to 15.00 would give Mug and Stapler a second match each. The gap between 4.99 and 5.00 would leave a price of 4.995 unmatched. Ranges determine the result just as keys do: a value can have zero, one, or several matches.

A report asking for “all customers, including customers without orders” needs a left outer join. LEFT JOIN keeps every left row. It attaches each matching right row, or supplies one row of NULL values when there is no match. The word OUTER is optional.

Follow the kept table

SELECT c.name, 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;

Ana still supplies two rows. A left join does not reduce a customer's orders to one. Chloe and Dev each supply exactly one row because neither has a match. There are five rows overall: three matching pairs and two unmatched customers.

The join returns at least as many rows as its left input before later filters. For each left row, count its matches; if it has none, count one NULL-filled row. This explains why four customers can produce five rows.

Guest order 104 is absent. Keeping every customer does not keep unmatched orders too. To keep every order, start with orders o LEFT JOIN customers c ON c.customer_id = o.customer_id. Selecting o.order_id, c.name then gives (101, Ana), (102, Ana), (103, Ben), and (104, NULL). Chloe and Dev no longer appear.

Find rows with no partner

A left anti-join keeps only rows that failed to match:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

The left join first makes all five rows. WHERE then keeps Chloe and Dev, whose order keys are NULL. Their missing partner is exactly what the query is looking for.

Choose a right-side column that cannot be NULL in a real match. An order's primary key is a good test. Testing an optional coupon_code would also keep real matching orders with no coupon, so it would not mean “no order.” Use IS NULL: = NULL evaluates to unknown and keeps no rows in WHERE.

The same pattern finds unsold products:

SELECT p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_id IS NULL;

It returns (Stapler), the only product with no order line. Here a real matching line necessarily has an order identifier, so the test detects the missing line.

Interpret a missing value before replacing it

A NULL-filled outer-join row means no partner matched. A stored NULL in an actual order can instead mean that a field was not supplied. These are different situations even though both display as NULL.

COALESCE(o.total, 0) chooses the first non-NULL argument. It would display 0 for Chloe and Dev. That can be helpful for a spending report, but a zero in a column labeled “order total” can suggest a real zero-value order. Keep the label and the meaning of the report aligned.

Sometimes the required rows sit on the right of a join. Sometimes both inputs must survive so that missing records can be compared. Right and full outer joins make these choices explicit.

Rewrite a right join as a left join

SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;

This keeps every order: (Ana, 101), (Ana, 102), (Ben, 103), and (NULL, 104). The right table is orders, so the guest order survives. Chloe and Dev are unmatched left rows and do not survive.

Swapping the inputs gives the equivalent orders o LEFT JOIN customers c ON o.customer_id = c.customer_id. Keeping the same explicit select list gives the same values. With SELECT *, the default column order can change. Using left joins throughout a report often makes the must-keep side easier to follow.

Keep missing rows from both inputs

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

The result contains (Ana, 101), (Ana, 102), (Ben, 103), (Chloe, NULL), (Dev, NULL), and (NULL, 104). These are three matching pairs, two unmatched customers, and one unmatched order: six rows. The customer columns are NULL for guest order 104; the order columns are NULL for Chloe and Dev.

A full join is useful for reconciliation, such as checking a warehouse stock list against a physical count. Matching keys appear together; keys found on only one side remain visible. In the store query, adding WHERE c.customer_id IS NULL OR o.order_id IS NULL keeps just Chloe, Dev, and order 104.

When reconciling two lists of the same kind of key, either input key can be NULL in the output. COALESCE(w.sku, k.sku) AS sku selects the available SKU from a warehouse alias w or count-list alias k. A combined key column lets the report identify records missing from either list.

Check syntax support

PostgreSQL, SQL Server, and Oracle support right and full outer joins. SQLite supports both beginning with version 3.39.0, released in 2022. Older SQLite versions reject them. MySQL supports right joins but has no FULL OUTER JOIN; a workaround combines left- and right-join results with UNION.

Older Oracle SQL may instead mark the optional side with (+) in WHERE. For example, WHERE c.customer_id = o.customer_id(+) preserves customers and fills missing order columns with NULL, like a left join from customers. The marker belongs to the side allowed to be missing. Use the named LEFT, RIGHT, and FULL join forms when writing new queries.

An outer join separates two decisions: which pairs count as matches, and which completed result rows the report keeps. ON makes the first decision. WHERE makes the second, after unmatched rows have received NULLs.

Keep everyone, but limit their matching orders

Suppose a report needs all customers and only their orders from March 10 onward:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.order_date >= '2026-03-10';

The date test is part of matching. Ana's order 101 is too early, but order 102 qualifies. Ben's order 103 qualifies. Chloe and Dev have no qualifying partner, so each receives a NULL-filled row. The result is (Ana, 102), (Ben, 103), (Chloe, NULL), and (Dev, NULL).

Filter the rows after the join

Move the same date test to WHERE:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-03-10';

Now the join first creates five rows, including the NULL-filled rows for Chloe and Dev. The date comparison is unknown for those NULL values, so WHERE removes them. It also removes Ana's early order. Only (Ana, 102) and (Ben, 103) remain.

The WHERE version produces the same matching rows as an inner join with that date filter.

Filter the kept table on purpose

A left-side condition in ON cannot remove left rows either. Consider ON o.customer_id = c.customer_id AND c.city = 'Denver'. Ana can match both orders, Chloe has none, and Ben and Dev fail the city part of every possible match. All four customers remain, with NULL order columns for Ben, Chloe, and Dev.

To request only Denver customers, put WHERE c.city = 'Denver' after the ordinary key condition. The result is (Ana, 101), (Ana, 102), and (Chloe, NULL). Chloe survives because the filter tests her own city, not a missing order field.

The anti-join test belongs after the join because it examines the NULL-filled result. Inner joins have no preserved unmatched rows, so moving a filter between ON and WHERE does not change their matching pairs. When reviewing an outer join, read each optional-side WHERE test and check what it does to a NULL-filled row.

An employee's manager is also an employee. A self join reads the same table under different aliases so one row can play the employee and another can play the manager. It uses ordinary join syntax; there is no SELF JOIN keyword.

Name the roles before the columns

The employee data is:

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id;

e supplies the employee row. Its manager_id points to the manager's emp_id in role m. Hassan points to 1, so he pairs with Grace; Iris also pairs with Grace. Jamal points to 2 and pairs with Hassan. The result is (Hassan, Grace), (Iris, Grace), and (Jamal, Hassan).

Grace has no manager. Her manager_id is NULL, so the inner join excludes her. Replacing JOIN with LEFT JOIN adds (Grace, NULL), producing four rows. Distinct aliases let the database tell the roles apart even though both inputs come from the same stored table.

Ask the question in the other direction

To find people who manage nobody, start with the manager role and look for a missing employee:

SELECT m.name
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.emp_id
WHERE e.emp_id IS NULL;

This returns (Iris) and (Jamal). Grace matches Hassan and Iris; Hassan matches Jamal. Iris and Jamal have no direct reports. Testing the employee key after the join finds their missing partner.

A third alias follows one more level. employees e JOIN employees m ON e.manager_id = m.emp_id JOIN employees g ON m.manager_id = g.emp_id, selecting their names, returns (Jamal, Hassan, Grace). Each additional role follows one more link. A fixed query reaches a fixed number of levels; a hierarchy of arbitrary depth needs a recursive query.

Pair colleagues once

The same table can also supply pairs of coworkers:

SELECT a.name, b.name
FROM employees a
JOIN employees b
  ON a.manager_id = b.manager_id
 AND a.emp_id < b.emp_id;

Hassan and Iris share manager 1. The less-than test excludes a person paired with themselves and keeps only one ordering of each pair. The result is (Hassan, Iris).

With only the manager equality, the result has five rows: Hassan–Hassan, Hassan–Iris, Iris–Hassan, Iris–Iris, and Jamal–Jamal. Grace's NULL manager key matches nothing, including another NULL. Replacing < with <> excludes self-pairs but keeps both (Hassan, Iris) and (Iris, Hassan). Choose < when the question needs each unordered pair once.

A cross join builds every pair of rows, with no matching condition. This is useful when the question asks for possibilities, such as every size–color combination a shop could stock.

Build the combination list

sizes contains S, M, and L. colors contains Red and Blue.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

The six pairs are (S, Red), (S, Blue), (M, Red), (M, Blue), (L, Red), and (L, Blue). Each size appears beside both colors. These are the result rows; their display order is not guaranteed without ORDER BY.

For two inputs, multiply their row counts. Three sizes and two colors give six rows. With a third table containing four options, the result has 24 rows. If any input is empty, no complete combination can be made and the result has zero rows.

Start with a complete reporting grid

Sales records show only combinations that occurred. To show every store in every month, first form a grid with stores CROSS JOIN months. Then use a left join to attach matching sales records:

SELECT s.store_id, m.month, x.total
FROM stores s
CROSS JOIN months m
LEFT JOIN sales x
  ON x.store_id = s.store_id
 AND x.month = m.month;

The grid keeps a store–month pair even when there is no sale, with NULL in x.total. Both key parts matter: matching only the store would attach that store's sales from other months too. The cross join defines which combinations the report must show, and the left join supplies available facts about them.

Spot an unintended cross join

FROM customers, orders without a joining condition makes 16 pairs from the four customers and four orders. Ana pairs with her own orders, Ben's order, and the guest order. Every customer does the same. That is useful only if every customer–order combination is actually wanted.

A large row count is a clue, but inspect the pairings as well. With 1,000 customers and 1,000 products, an intentional cross join makes 1,000,000 rows. A join with a wrong but selective condition can also return a smaller, incorrect result. Compare the count and the meaning of each pair with the question.

Recognize equivalent forms

CROSS JOIN, a comma join without a matching condition, and an inner join with ON 1 = 1 all keep every pair. SQLite and MySQL also accept a plain JOIN with no ON and treat it as a cross join; PostgreSQL requires ON or USING for a plain join. Write CROSS JOIN when every combination is intentional.

DISTINCT removes identical selected rows, not incorrect relationships. If an accidental cross join pairs Ana with Ben's order, those values remain a distinct row. Repair the missing condition instead of hiding some repeated output.

A student can take several courses, and a course can have several students. A junction table records these many-to-many links as individual pairs. It is also called a bridge, link, or associative table.

Read one link per row

The example data contains students 1 Mia, 2 Noah, and 3 Omar; courses 10 Algebra, 20 Biology, and 30 Chemistry; and enrollment pairs (1, 10), (1, 20), and (2, 10).

Student 1 appears twice because Mia takes two courses. Course 10 appears twice because Algebra has two students. Neither column must be unique on its own. The pair can form a composite primary key, PRIMARY KEY (student_id, course_id), preventing the same student from being enrolled in the same course twice.

A link can have its own facts. enrolled_on belongs to the enrollment because it describes when a particular student joined a particular course. Likewise, order_items.quantity describes how much of a particular product belongs to a particular order.

Cross the junction with two joins

SELECT s.name, c.title
FROM students s
JOIN enrollments e ON e.student_id = s.student_id
JOIN courses c ON c.course_id = e.course_id;

The first condition attaches each student's enrollment rows. The second replaces each enrollment's course reference with facts from the course table. The complete result is (Mia, Algebra), (Mia, Biology), and (Noah, Algebra). Each row stands for one enrollment.

There is no student-to-course key in either endpoint table. Matching s.student_id = c.course_id would compare unrelated identifiers and skip the enrollment facts. The junction table supplies the relationship.

The same path works in either direction. Add WHERE c.title = 'Algebra' to obtain (Mia, Algebra) and (Noah, Algebra). Add WHERE s.name = 'Mia' instead to obtain (Mia, Algebra) and (Mia, Biology). The links stay the same; the filter selects which links answer the question.

Include people or courses without links

Start from the side that must survive. students s LEFT JOIN enrollments e ON e.student_id = s.student_id keeps Omar with NULL enrollment columns. To show course titles while keeping him, the later join from enrollments to courses must also preserve the missing enrollment.

To find courses nobody takes, use the course side and an anti-join:

SELECT c.title
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.course_id
WHERE e.student_id IS NULL;

This returns (Chemistry). In a real matching enrollment, student_id is part of the primary key and cannot be NULL, so the test identifies the missing link.

Store one relationship per junction row. A text list such as "10,20" in one student field cannot be matched by equality to individual course IDs 10 and 20. Separate pairs let each join condition compare one reference with one key.

A longer join follows the same rule as a two-table join: attach the next table using a meaningful condition. Before writing the query, trace the keys and decide what one result row should represent.

Add the detailed store data

The order lines (order_id, product_id, quantity) are (101, 1, 2), (101, 2, 4), (102, 3, 6), (103, 1, 6), (103, 2, 8), and (104, 2, 10). Each line links an order to a product and records a quantity.

For the question “What did each customer buy?”, follow customers → orders → order_items → products. The keys along the path are customer ID, order ID, and product ID. Four tables along this chain require three links.

Attach one new table at a time

SELECT c.name, o.order_id, p.name AS product,
       oi.quantity, oi.quantity * p.price AS line_total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
ORDER BY o.order_id, p.product_id;

Each ON refers to the newly introduced table and one already available. Products attach through order items, not directly through customers. The result is:

The first join excludes guest order 104 because its customer reference is NULL. The remaining three orders contain five lines, so the final result has five rows. For order 101, two mugs cost 16.00 and four pens cost 6.00, matching its order total of 22.00.

Know what a row counts

One result row represents an order line. Ana and order 101 appear twice because that order has two products. Repeated customer names are therefore expected. Counting these rows counts lines, not distinct customers or orders.

A filter may refer to any joined table. Adding WHERE p.name = 'Pen' before ORDER BY keeps (Ana, 101, Pen, 4, 6.00) and (Ben, 103, Pen, 8, 12.00). No product filter can restore the guest order already excluded at the customer link.

Keep each link visible

For a chain of inner joins, the written table order can change without changing the matching combinations, provided each condition refers only to tables already introduced. Starting with the subject of the report and following its keys is usually easiest to read.

A missing condition can multiply an intermediate result in SQLite or MySQL, which accept a plain JOIN without ON. PostgreSQL and SQL Server reject that syntax. A missing condition in a comma-style query can also create every combination at that step.

Sometimes one table serves two roles. A flight report can join airports as dep on dep.code = f.from_code and again as arr on arr.code = f.to_code. Distinct aliases give the departure and arrival facts separate places in the result.

A left join preserves rows at one step. Later joins and filters still act on that step's result. To keep an unmatched parent through a longer path, follow what happens to its NULL-filled columns at every stage.

See where a kept row disappears

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id;

The first step makes five rows: Ana's two orders, Ben's order, and one NULL-filled row each for Chloe and Dev. At the next step, Chloe's and Dev's o.order_id values are NULL. They cannot satisfy the equality with an order-line key, so the inner join removes them. The query returns five order-line rows and loses both customers without orders.

Preserve the optional path

SELECT c.name, o.order_id, oi.product_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.order_id;

Now the second join preserves those NULL-filled rows too. The result has (Ana, 101, 1), (Ana, 101, 2), (Ana, 102, 3), (Ben, 103, 1), (Ben, 103, 2), (Chloe, NULL, NULL), and (Dev, NULL, NULL): seven rows.

When later joins depend on an optional table's columns, they usually need to remain left joins to keep missing partners. A later inner join on a required table's columns has a different role, so examine the actual dependency in each condition.

Follow the same rule through a junction

SELECT s.name, c.title
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id
LEFT JOIN courses c ON c.course_id = e.course_id;

This returns (Mia, Algebra), (Mia, Biology), (Noah, Algebra), and (Omar, NULL). Making the last join an inner join removes Omar because his missing enrollment cannot match a course.

Parentheses offer another way to express the grouping: students s LEFT JOIN (enrollments e JOIN courses c ON c.course_id = e.course_id) ON e.student_id = s.student_id. The enrollment–course matches form the optional group; the outer join attaches that group to every student, preserving Omar.

Check later filters and join direction

Adding WHERE c.title = 'Algebra' to the all-left student query leaves only Mia and Noah in Algebra. The comparison is unknown for Omar's NULL course title. This answers “Who takes Algebra?” but no longer answers “Show all students and their courses.” An IS NULL test behaves differently because it deliberately accepts a missing value.

A later right join changes the kept side again: it preserves the newly added right table, and earlier unmatched rows can disappear. Put the must-keep table first and attach optional paths with left joins when that expresses the report clearly.

Build and inspect the query one join at a time. Note both the row count and which keys remain. A falling count can reveal lost unmatched rows; an unexpected jump can reveal a missing key part. Some counts stay unchanged when rows are both lost and multiplied, so inspect representative keys as well.

A join can be correct while an aggregate over its result is wrong. The key question is what one row represents after the join. A value stored once per order can appear several times in a list of order lines.

Follow a repeated order total

Ana has order 101 for 22.00 and order 102 for 25.50. Adding the two orders gives 47.50. Order 101 has two lines, so joining to order_items repeats 22.00 twice; order 102 has one line. Adding the repeated values gives 69.50.

SELECT c.customer_id, c.name, SUM(o.total) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

GROUP BY collects rows for each customer, and SUM adds that group's values. This query returns (1, Ana, 47.50) and (2, Ben, 60.00). Adding the order-line join before grouping would inflate Ana to 69.50 and Ben to 120.00. The two customers' totals would become 189.50 instead of 107.50.

Across all four orders, including guest order 104, joining orders directly to their lines makes SUM(o.total) return 204.50 instead of 122.50. This fan-out comes from the one-to-many link, not from a syntax error.

Sum facts at their stored level

For order totals, sum orders without adding the detail table. For a line-value total, calculate each line's own value:

SELECT SUM(oi.quantity * p.price) AS sales
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id;

The six line amounts are 16.00, 6.00, 25.50, 48.00, 12.00, and 15.00. They add to 122.50, the total of all four orders. Each quantity–price product belongs to one line, so each is added once.

Count real matches

After customers join to orders and lines, Ana occupies three rows. COUNT(*) counts those lines; COUNT(DISTINCT o.order_id) counts her two distinct orders.

After just a left join from customers to orders, COUNT(*) counts one NULL-filled row for Chloe and one for Dev. Count the optional order key instead:

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

This returns (1, Ana, 2), (2, Ben, 1), (3, Chloe, 0), and (4, Dev, 0). COUNT(column) ignores NULL values; COUNT(*) counts rows.

Two many sides multiply each other

If a customer has three orders and two tickets, joining both child tables independently to that customer makes six rows. Every order pairs with every ticket. Plain counts of either child key become six. Count distinct child keys to obtain three and two, or total the child tables separately.

DISTINCT c.customer_id, c.name is useful for a unique customer list. Keeping the key avoids merging different customers who share a name. It does not repair a missing join condition. SUM(DISTINCT o.total) is also unsafe: it merges two separate orders that happen to cost the same amount.

A query can run and still describe the wrong relationships. Before using a join result, predict what should survive, how often each key should appear, and what one result row should mean.

Make a row-count prediction

If an order's customer reference matches a unique customer key, each order has at most one customer partner. An inner join therefore returns at most one row per order. In the store data, three of four orders have a customer, so the customer–order inner join returns three rows.

From the customer side, a left join returns the three matched pairs plus one row for Chloe and one for Dev: five rows. A cross join of the four customers and four orders gives 16. These predictions provide different checks for different join types; the number of tables alone does not determine the count.

Investigate the shape of a wrong result

Matching only store_id when records are identified by (store_id, month) mixes different months. Match every component with AND. A query that happens to work while a store has one month can fail as soon as a second month is loaded.

Separate missing values from bad keys

With ordinary equality, NULL keys do not match, including NULL against NULL. Joining orders to themselves on customer_id gives four pairs from Ana's two orders and one from Ben's order, for five rows. The guest order contributes none. An outer join is needed when a row with a missing reference must survive.

Duplicate lookup values multiply only the matches for those values. If a country code occurs twice in a lookup table, each customer with that code gets two partner rows. Codes that occur once still give one partner. Inspect a lookup expected to be unique with:

SELECT code, COUNT(*) AS copies
FROM countries
GROUP BY code
HAVING COUNT(*) > 1;

GROUP BY collects each code's rows; HAVING keeps groups whose count exceeds one. Loading the entire lookup twice would double every matching customer's rows.

Also inspect key types and formats. Text '0042' and the number 42 can produce different behavior across databases: no match, a type error, or an automatic conversion. SQLite can match a TEXT column containing '0042' to an INTEGER column containing 42. Use consistent key types and formats, or an explicit conversion chosen for the intended meaning.

Work through the report's requirements

Ask which table must keep all rows. Trace its full path of keys, and check each condition against the schema. Decide why each link is inner or outer, then check whether each filter belongs in matching or after matching. Compare row counts after each step and inspect a known matched row, an unmatched row, and a row with several partners. Finally, check that each count and sum measures the intended kind of record.