SQL joins explained: which customers ordered, and which did not?

Start with the business question, not the join name

This is an evergreen SQL explainer, not a claim about a new database feature. Imagine a fictional shop with a customers table and an orders table. A manager asks two questions: which customers placed an order this month, and which customers did not? The questions sound similar, but they require different treatment of unmatched customers.

PostgreSQL describes a join query as combining rows from two tables according to an expression that decides which rows pair together. In this example, customers.id matches orders.customer_id. Writing that relationship explicitly with JOIN and ON makes the intended pairing easier to review than mixing it into a longer WHERE clause.

Source: PostgreSQL 18 documentation: Joins Between Tables

INNER JOIN answers who has a match

Suppose customers contains Asha, Bala and Charu. The orders table contains two orders for Asha and one for Charu. An inner join on the customer identifier returns the matching combinations: two rows for Asha and one for Charu. Bala disappears because there is no matching order row.

The query is: SELECT c.id, c.name, o.id AS order_id FROM customers AS c INNER JOIN orders AS o ON o.customer_id = c.id. PostgreSQL documents INNER as the default qualified join: each left-side row appears once for every right-side row that satisfies the join condition. That last phrase matters because a join does not automatically return one row per customer.

Source: PostgreSQL 18 documentation: Table Expressions

LEFT JOIN keeps the customers who did not order

To answer the second question, start from customers and use a left join: SELECT c.id, c.name, o.id AS order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.id. Asha and Charu still match their orders, while Bala remains with a null order_id. The preserved table is the one written on the left.

To list only customers without orders, add WHERE o.id IS NULL after the join. Do not use WHERE o.status = 'paid' if you intend to retain customers without a paid order; that condition rejects the null rows created by the outer join. If the question is about matching paid orders while retaining every customer, put o.status = 'paid' in the ON condition instead. PostgreSQL’s documentation shows that ON filters are applied as part of matching, while WHERE filters act after an outer join and can therefore change the result.

Source: PostgreSQL 18 documentation: Joins Between TablesPostgreSQL 18 documentation: Table Expressions

A practice exercise with five expected checks

Create two small temporary tables or use a disposable local database. Add three fictional customers, two orders for the first customer, no order for the second and one order for the third. Before running SQL, write the expected output rows on paper. This gives you an independent result against which to review the query.

Run an inner join and a left join, then explain why their row counts differ. Change one order’s customer_id to a value that has no customer and observe which questions reveal that orphaned record. A left join starting from customers will not show an unmatched order; investigating both sides requires a different query, such as a full outer join where supported.

• Confirm that the inner join excludes the customer with no order.
• Confirm that the left join keeps all three customers.
• Count distinct customer IDs as well as result rows; explain why the numbers differ.
• Move a paid-status condition between ON and WHERE and record the changed output.
• Replace SELECT * with named columns so duplicated identifiers are unambiguous.

Source: PostgreSQL 18 documentation: Table Expressions

The common mistake is forgetting the result grain

If one customer has two orders, the join produces two customer-order combinations. Summing a customer-level value such as a credit limit after that join could count the same value twice. Before calculating a metric, state the grain in one sentence: one row per order, one row per customer, or one row per customer per month. Then aggregate at the intended grain and compare totals with a simple control query.

Aliases such as c and o keep conditions readable, but short names do not replace clear logic. Avoid NATURAL JOIN in a learning or production example because PostgreSQL warns that a future shared column name can silently become another matching condition. Explicit ON clauses make the relationship visible during review.

Source: PostgreSQL 18 documentation: Table Expressions

What this exercise proves and what it does not

Completing the exercise shows that you can translate two business questions into join behaviour, predict unmatched rows and investigate duplicate multiplication. It does not prove that the query is fast on a large dataset. Performance also depends on table sizes, indexes, statistics, filters and the database planner, so avoid making speed claims from a three-row practice table.

For a portfolio, keep the sample data, both queries, expected results and a short note about the ON-versus-WHERE experiment. Learners in Chennai or online can use this as a starting exercise for SPOTHUB’s Data Analytics + AI learning path. Training and project practice can support skill development, but they do not guarantee employment or a particular outcome.

Sources and further reading

• PostgreSQL 18 documentation: Joins Between Tables · checked 2026-09-21
• PostgreSQL 18 documentation: Table Expressions · checked 2026-09-21

Spot an error? Email info@spothub.in with the article link and correction.