SQL JOIN and LEFT JOIN with checked results
Learn SQL JOIN and LEFT JOIN with a runnable PostgreSQL example. Check missing matches, repeated rows, and filters to choose the right join for your report.

In PostgreSQL, SQL JOIN and LEFT JOIN differ in whether they keep rows without a match: JOIN returns matching row pairs only, while LEFT JOIN also keeps unmatched rows from the left table. For an unmatched row, LEFT JOIN supplies NULL in the right-side columns. Use JOIN for customers with orders and LEFT JOIN when customers without orders must remain visible.
The difference in returned rows
JOIN means INNER JOIN in PostgreSQL, and LEFT OUTER JOIN means the same thing as LEFT JOIN. The important choice isn't the longer spelling; it's whether your report must preserve unmatched rows.
A join connects rows using a matching condition, such as the same customer identifier in two tables. Each matching pair produces an output row. If one customer has two orders, both join types return two rows for that customer.
Before running a query, write a match ledger: a small record of how many matches each customer has and how many output rows you expect. The ledger helps you distinguish a missing customer from a legitimate repeated customer name.
For each row in the left table:
- JOIN produces one output row for every matching right-side row, or no output row when there isn't a match.
- LEFT JOIN produces one output row for every matching right-side row, or one preserved row when there isn't a match.
- Neither join type promises one output row per customer.
Three customers and a match ledger
The example uses three fictional customers with two, one, and zero orders, so you can see both repeated matches and an unmatched customer. Every query below uses PostgreSQL syntax.
customers
customer_id | customer_name
1 | Ava
2 | Bo
3 | Cy
orders
order_id | customer_id | status
101 | 1 | open
102 | 1 | closed
103 | 2 | open
The matching key is customer_id, the identifier that connects an order to a customer. Don't match on customer names: different customers can share a name, and a spelling change shouldn't break the connection.
Count the orders for each identifier before writing the join. Ava has two matches, Bo has one, and Cy has none. Your ledger should predict the following results.
customer | matching orders | JOIN rows | LEFT JOIN rows
Ava | 2 | 2 | 2
Bo | 1 | 1 | 1
Cy | 0 | 0 | 1
Total | 3 | 3 | 4
The queries use common table expressions, which are named query results introduced by WITH and available within the statement. VALUES supplies the fictional records directly. You can copy each query into a PostgreSQL query editor and run it independently without creating permanent tables.
Practice what this post teaches
Two joins against the same records
Changing JOIN to LEFT JOIN adds Cy's unmatched row without removing either of Ava's matching orders. Run the matching-only query first and compare its output with the ledger.
Matching customers and orders
WITH customers(customer_id, customer_name) AS (
VALUES (1, 'Ava'), (2, 'Bo'), (3, 'Cy')
), orders(order_id, customer_id, status) AS (
VALUES (101, 1, 'open'), (102, 1, 'closed'),
(103, 2, 'open')
)
SELECT c.customer_name, o.order_id
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;
-- Result:
-- customer_name | order_id
-- Ava | 101
-- Ava | 102
-- Bo | 103
The aliases c and o are short names for customers and orders. ON supplies the matching condition. ORDER BY makes the displayed order predictable; without ORDER BY, don't rely on the database returning rows in a particular sequence.
Cy doesn't appear because no order has Cy's customer identifier. Ava appears twice because two different orders match Ava. Both observations agree with the ledger.
Try predicting matching rows yourself with INNER JOIN: combine matching rows before running another query.
Every customer, including unmatched customers
WITH customers(customer_id, customer_name) AS (
VALUES (1, 'Ava'), (2, 'Bo'), (3, 'Cy')
), orders(order_id, customer_id, status) AS (
VALUES (101, 1, 'open'), (102, 1, 'closed'),
(103, 2, 'open')
)
SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;
-- Result, with NULL written explicitly:
-- customer_name | order_id
-- Ava | 101
-- Ava | 102
-- Bo | 103
-- Cy | NULL
Customers is the left table because it appears before LEFT JOIN. Cy now contributes one preserved row. NULL means there's no matching order value in that row, not that Cy has an order numbered zero. Your query editor might display NULL as an empty cell or another marker.
Practice preserving unmatched rows with LEFT JOIN: keep rows without a match. Write your expected row count before you run the query.
Open orders without lost customers
To keep every customer while showing only open orders, put the order-status condition in ON alongside the matching key. The condition changes which orders qualify as matches, not which customers must remain.
WITH customers(customer_id, customer_name) AS (
VALUES (1, 'Ava'), (2, 'Bo'), (3, 'Cy')
), orders(order_id, customer_id, status) AS (
VALUES (101, 1, 'open'), (102, 1, 'closed'),
(103, 2, 'open')
)
SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
AND o.status = 'open'
ORDER BY c.customer_id, o.order_id;
-- Result, with NULL written explicitly:
-- customer_name | order_id
-- Ava | 101
-- Bo | 103
-- Cy | NULL
Ava's closed order no longer qualifies. Ava and Bo each have one open order, while Cy still has no qualifying order. The filtered ledger therefore predicts one output row per customer for these particular records.
The common mistake is moving the open-status condition from ON to WHERE. WHERE filters rows after the join has formed its result. Cy's preserved row has NULL for the order status, and comparing NULL with the text 'open' doesn't evaluate to true. WHERE keeps only rows whose condition evaluates to true, so Cy disappears.
For these records, putting the open-status condition in WHERE would return only Ava with order 101 and Bo with order 103. If the task says every customer must remain, that two-row result contradicts the ledger. Move the right-side status restriction back into ON.
Checks before using the result
Check both the intended meaning of each output row and the expected contribution from each customer before using a joined result. The original queries return customer-order pairs, with an extra unmatched-customer row in the LEFT JOIN result.
Use these checks when a result looks wrong:
- Missing customer: confirm whether the task requires JOIN or LEFT JOIN, then inspect right-side conditions in WHERE.
- Repeated customer: compare order identifiers and count the customer's matching orders.
- Unexpected NULL: check whether any right-side row satisfies every condition in ON.
- Unexpected total: compare each customer's actual row count with the ledger instead of checking only the overall count.
Don't add DISTINCT just because Ava appears twice. DISTINCT removes duplicate selected rows; it doesn't decide which order belongs in a customer summary. With the order identifier selected, Ava's rows are already different. A one-row-per-customer summary needs a separate rule, often aggregation, which combines rows into summaries.
For a final exercise, change Bo's order status from open to closed in the filtered example. Predict the result before running the query: the total should remain three rows, but Bo's order identifier should become NULL.
Use the same predict-then-check method with Practice: combine tables on The Work Gym. Compare the checked result with your ledger to locate the mistake.
WITH customers(customer_id, customer_name) AS (
VALUES (1, 'Ava'), (2, 'Bo'), (3, 'Cy')
), orders(order_id, customer_id, status) AS (
VALUES (101, 1, 'open'), (102, 1, 'closed'),
(103, 2, 'closed')
)
SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
AND o.status = 'open'
ORDER BY c.customer_id, o.order_id;
-- Result, with NULL written explicitly:
-- customer_name | order_id
-- Ava | 101
-- Bo | NULL
-- Cy | NULL
Bo has an order in the starting data, but no order that qualifies under the complete ON condition. That's why Bo now receives a preserved row rather than order 103.
The ledger method checks row preservation and multiplication. It doesn't cover aggregation or query performance, and counting matches manually becomes slow on large tables. Use a small, inspectable set of records to verify the join's logic before applying that logic to a larger report.
Common questions
Is JOIN the same as INNER JOIN in PostgreSQL? Yes. In PostgreSQL, JOIN without another join type means INNER JOIN, which returns matching row pairs only. Writing INNER JOIN explicitly can make your intention clearer to another reader.
Is LEFT JOIN different from LEFT OUTER JOIN? No. In PostgreSQL, LEFT JOIN and LEFT OUTER JOIN mean the same thing. Both preserve unmatched left-side rows and supply NULL for their right-side columns.
Why does a LEFT JOIN return more rows than the left table? A left-side row produces one output row for each matching right-side row. If one customer has several matching orders, that customer appears several times. Preserving customers doesn't mean returning each customer only once.
Why does a WHERE condition remove unmatched LEFT JOIN rows? WHERE keeps only rows whose condition evaluates to true. An unmatched row has NULL in the right-side columns, so an equality comparison against a right-side status doesn't evaluate to true. Put the right-side restriction in ON when you need to preserve every left-side row.