SQL JOIN types: how I combine tables and check the result

JOIN looks harmless when I first meet it. Customers live in one table, orders in another, and an identifier connects them. Then part of the customer population disappears from the report while everyone else seems to have spent twice as much. Sales is preparing a celebration. I am still inspecting the query.

That is why I learn JOIN types together with questions about their output. Which records will remain? How many times can a source record appear? What does a missing match mean? Knowing where to type LEFT JOIN does not prevent a very convincing mistake.

I will use one small synthetic dataset throughout this article. The examples run in PostgreSQL, with temporary tables created in a separate practice session. I deliberately include a customer without purchases and orders without matching customers. Those awkward records make the differences visible without needing thousands of rows.

I define what one row represents

Before joining anything, I write down two statements: one customer-table row represents a customer, and one order-table row represents an order. This feels almost too obvious to mention. It is also exactly the kind of obvious detail that returns later as a three-hour meeting about inconsistent revenue.

A customer can place several orders. After combining the tables, their name appears beside every matching order. I should no longer expect one row per customer. I expect a row for each matching pair, unless I perform a subsequent aggregation.

The condition ON c.customer_id = o.customer_id says when two source rows match. The column names can differ, and the condition can involve several columns. If customer identifiers are unique only within a company, I may need the company identifier as well. Customer 17 in one system is not necessarily customer 17 in another.

I inspect data types and meaning too. An order number and a customer number can contain identical integers without describing the same relationship. The database cannot infer my intention from the fact that I look quite serious while writing the query. It evaluates the condition I provide.

The PostgreSQL documentation on table expressions defines the behaviour precisely. My practical starting point is to state the intended output level and identify a few records that should appear. Then I choose the join type that supports that intention.

I prepare data with missing and repeated matches

I run the following block once in a fresh session. There are three customers and five orders. Ada has two orders, Ben has one, and Celina has none. Order 104 refers to customer 9, who is absent from this extract. Order 105 has no customer identifier.

-- example: fixture
CREATE TEMP TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL
);
CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount INTEGER NOT NULL,
    status TEXT NOT NULL
);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Celina');
INSERT INTO orders VALUES
    (101, 1, 100, 'completed'),
    (102, 1, 50, 'cancelled'),
    (103, 2, 80, 'completed'),
    (104, 9, 40, 'completed'),
    (105, NULL, 20, 'completed');

I deliberately omit a foreign-key constraint from orders because this exercise needs an inconsistent extract. An enforced foreign key to the customer table would reject a reference to a nonexistent customer 9. Missing matches can still arise in exports, intermediate datasets or integrations between systems.

The amounts are arbitrary units. Currency and tax treatment would require their own definitions in a real report, but here I am isolating row behaviour. The cancelled status will become useful when I distinguish matching conditions from filters. Initially, I include all orders regardless of status.

The small dataset lets me predict each answer before executing the code. I write down the expected row count and a few identifiers. A successful execution message confirms that the engine accepted my instructions. It says remarkably little about whether I asked a sensible question.

INNER JOIN returns matching pairs

-- example: inner
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id;

I receive orders 101, 102 and 103. Ada appears twice, Ben once. Celina is absent because she has no order. Orders 104 and 105 disappear because no customer satisfies the matching condition for them. The result contains three rows.

I avoid describing this as the intersection of customers and orders. Those tables represent different kinds of objects. Thinking about matching pairs is more useful, especially when both tables contain several rows for the same join key. A diagram of two overlapping circles does not explain that multiplication particularly well.

I choose INNER JOIN when a missing match should genuinely exclude the source record. For example, I may want orders with an identified customer. If I need a report of every order, the same query is incomplete. Correctness comes from the question, rather than the popularity of a particular join type.

Before calculating further, I compare the input and output counts and amounts. All five orders sum to 290. Orders with a matching customer sum to 230. The difference of 60 is explained by orders 104 and 105. I can point to the actual records instead of vaguely suggesting that something probably failed to synchronise.

This comparison does not tell me whether losing those orders is acceptable. That is a separate decision about the report’s scope. I might exclude them from customer segmentation while reporting them separately as a data-quality exception. The important part is making the exclusion visible.

LEFT and RIGHT JOIN preserve a chosen side

-- example: left
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Now I get four rows. Ada still has two matches and Ben has one. Celina receives a row with a missing order side: order_id and amount are NULL. LEFT JOIN preserves every customer, but it does not promise exactly one output row for each customer.

In this result, the NULLs indicate that no order supplied those values. I do not automatically replace every NULL with zero. A missing order identifier and an order whose amount is zero mean different things. Zero can be appropriate later when I calculate a customer-level order count.

RIGHT JOIN preserves the table on the right. Here I reverse the table order so that the intended population remains the same:

-- example: right
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM orders AS o
RIGHT JOIN customers AS c ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

The output is the same four rows. I often prefer LEFT JOIN because a consistent preserved side makes a longer query easier to read. That is a readability preference, not a claim that rewriting RIGHT as LEFT automatically makes a database faster.

When several joins are involved, I revisit the population after each step. Preserving customers in the first join does not guarantee that a later INNER JOIN will preserve them too. I read the whole chain and inspect intermediate results when the relationship becomes difficult to follow.

ON and WHERE can answer different questions

Suppose I want every customer and only their completed orders. I place the status requirement in the matching condition:

-- example: left-completed
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status = 'completed'
ORDER BY c.customer_id, o.order_id;

There are three rows: Ada with 101, Ben with 103, and Celina without a match. Moving the status condition to WHERE would remove Celina. Comparing her missing order status with completed does not evaluate to true.

-- example: where-completed
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
ORDER BY c.customer_id, o.order_id;

This version returns two rows. I do not memorise a rule that WHERE always breaks a LEFT JOIN. A filter on a customer attribute may be exactly what the analysis requires. I inspect the actual condition and ask whether it rejects rows whose matching side is missing.

A useful additional case is a customer with only cancelled orders. The first query retains that customer with no completed match. The second removes them. Thinking through this case makes the intended report population much clearer than just moving clauses until the output looks plausible.

FULL JOIN exposes missing matches on both sides

-- example: full
SELECT c.customer_id AS known_customer_id,
       o.customer_id AS order_customer_id,
       o.order_id
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY COALESCE(c.customer_id, o.customer_id), o.order_id;

The result contains six rows: three matched orders, Celina without an order, and two orders without recognised customers. I retain both customer identifiers separately. That preserves the distinction between the absent customer 9 and the missing identifier on order 105.

FULL JOIN is useful when reconciling datasets. I might compare two extracts and need records appearing only on either side as well as the matches. Before comparing totals, I check key uniqueness on both sides. Repeated keys can multiply records in a FULL JOIN just as they can in other joins.

The expression NULL = NULL does not become true because both values are missing. The PostgreSQL comparison documentation also describes IS NOT DISTINCT FROM, which can treat two NULLs as equal. I use that behaviour only when it fits the model. Joining every unknown customer to every other unknown customer would create a very sociable error.

I also distinguish a missing match from missing attributes in a matched row. A customer can exist while their optional address is NULL. Testing a non-nullable key from the matched table is generally a clearer way to identify absent records than testing an optional descriptive field.

CROSS JOIN produces every combination deliberately

-- example: cross
SELECT c.customer_id, o.order_id
FROM customers AS c
CROSS JOIN orders AS o
ORDER BY c.customer_id, o.order_id;

Three customers multiplied by five orders produce fifteen rows. Every person is paired with every order, regardless of who placed it. That is unsuitable for attributing customer purchases. It is a perfectly clear demonstration of a Cartesian product.

A useful application is a complete grid of categories and periods. If a report must show every product in every month, including months without sales, I can construct those combinations before attaching actual results. I first restrict both inputs to the products and months that belong in the analysis.

I estimate the size before execution. A thousand products and twelve months produce twelve thousand combinations. A million customers and a million events produce a trillion. The database does not need encouragement to appreciate the difference. It needs a more carefully scoped query.

When a join suddenly produces a huge result, I also check incomplete composite keys and conditions that are always true. An accidental Cartesian product does not always announce itself with the words CROSS JOIN. Sometimes it arrives dressed as a perfectly respectable business query.

If either input is empty, the CROSS JOIN returns no combinations. That matters when I build reporting scaffolds: a missing calendar or category table can make the entire report disappear. I validate those inputs as well as the final totals.

Multiple matches explain duplicated revenue

Now I introduce customer tags. Ada has two tags and Ben has one. That is a legitimate relationship, but each of Ada’s order amounts appears once for each tag.

-- example: multiplied
WITH customer_tags(customer_id, tag) AS (
    VALUES (1, 'new'), (1, 'vip'), (2, 'new')
)
SELECT o.order_id, o.amount, t.tag
FROM orders AS o
JOIN customer_tags AS t ON t.customer_id = o.customer_id
ORDER BY o.order_id, t.tag;

I receive five rows. Adding the displayed amounts gives 380 instead of 230 for the same three orders. The amounts 100 and 50 each appear twice. The JOIN is following its definition; the analytical mistake occurs when I treat this result as a table with one row per order.

I resist reaching immediately for SELECT DISTINCT. Two genuinely different orders may have identical amounts, so SUM(DISTINCT amount) can remove real sales. DISTINCT across all output columns would retain the two different tags anyway. I need to establish what the question actually requires.

If I only want orders belonging to customers with any tag, EXISTS is sufficient. It checks for a matching record without adding that record to the output:

-- example: exists
WITH customer_tags(customer_id, tag) AS (
    VALUES (1, 'new'), (1, 'vip'), (2, 'new')
)
SELECT o.order_id, o.amount
FROM orders AS o
WHERE EXISTS (
    SELECT 1 FROM customer_tags AS t
    WHERE t.customer_id = o.customer_id
)
ORDER BY o.order_id;

I am back to three orders totalling 230. If I need revenue by tag, I must separately decide how amounts belong to overlapping groups. SQL cannot decide whether each tag receives the whole amount or whether the amount should be allocated between tags.

Another useful approach is aggregating a detail table to the required level before joining it. For example, I might calculate an order-level total from order lines first. That works when the intended output genuinely needs one summary row per order. I would still check that the grouping key captures the full identity of an order.

I check the complete key and the relevant point in time

In this exercise, a customer identifier is unique across the whole customers table. In an actual model, I verify that assumption. Imagine two fictional shops numbering customers independently. Customer 1 in shop A and customer 1 in shop B represent different people. Joining only on the number could assign an order to both people while remaining perfectly valid SQL.

The match would need to include the shop, and the relevant combination of columns must be unique wherever I expect a single record. I check the data model and the system’s rules first. Adding conditions at random until the row count looks reasonable provides little assurance that the result means what I think it means.

A similar problem arises when a table stores history. One customer may have several successive addresses or statuses. To reconstruct the situation on the purchase date, I need the version valid at that moment. A report based on current status selects a different record. Both questions can be useful, but they require different matching logic.

For validity periods, I also check boundaries. Two periods should not accidentally cover the same instant when the model allows only one active version. Otherwise, a single order receives multiple matches again. Before building the full report, I test an event exactly on a boundary and an event without an applicable version.

These examples explain why knowing JOIN names is insufficient. A crucial part of the condition may be hidden in the key definition or the question about time. The database will execute a poorly expressed intention. It does not report an error merely because the result makes no business sense.

I investigate a long query one join at a time

When a query combines several tables and produces a suspicious result, I begin with its starting dataset. I check the order count and amount before the first JOIN. Then I add the next join and repeat the checks, looking for the exact stage where the represented objects or total change.

If the row count increases, I inspect one specific identifier. Two rows for the same order often explain more than an entire export. I might find different tags, historical versions or accidentally combined shops. Once I understand the cause, I can decide whether the multiplicity is expected and how it belongs in the report.

If records disappear, I examine unmatched keys and filters. I pay attention to types, formatting and missing values. I do not automatically trim every column or convert everything to text. I first establish whether the issue comes from input quality or choosing the wrong field for the relationship.

I keep identifiers from both sides in the working result because they make matches traceable. I may hide technical columns in the final presentation, but they are valuable during diagnosis. I also avoid an unnecessarily wide SELECT * when repeated column names make the origin of a value unclear.

Only after confirming the logic do I investigate performance: input size, necessary columns, filters and the execution plan. An index may help locate matching records. It cannot repair an incorrect relationship. Producing overstated revenue faster still leaves me with overstated revenue, just slightly earlier.

I leave a short explanation beside the solution. A note that the tags table has several records per customer while the report needs one per order can prevent the next person from rediscovering the same trap. A month later, that next person is very often me.

I validate the result before making a chart

My final customer report includes completed orders and preserves people without purchases. I count non-null order identifiers and replace a missing sum with zero only in the resulting measure.

-- example: summary
SELECT c.customer_id,
       COUNT(o.order_id) AS order_count,
       COALESCE(SUM(o.amount), 0) AS total_amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status = 'completed'
GROUP BY c.customer_id
ORDER BY c.customer_id;

The results are Ada with one order and 100, Ben with one order and 80, and Celina with zero orders and zero amount. COUNT(*) would count Celina’s row created by the LEFT JOIN. The distinction between counting all rows and counting non-null values is documented under aggregate functions.

I inspect three things: the number of unique business objects, the total measure, and missing matches. Each check answers a different question. Matching totals alone do not prove that every record reached the correct place. Two mistakes can occasionally cancel each other out with impressive timing.

For an exercise, I would add another completed order for Ada with an amount of 100. Her correct total becomes 200 and her order count becomes two. Then I would add a fourth customer without purchases. That person should remain in the report with zeros. These checks test the logic rather than merely confirming that SQL still executes.

Finally, I write down the population, included statuses and meaning of each output row. My SQL histogram tutorial continues from customer-level results to a purchase distribution. Once the data is joined correctly, I can build meaningful business KPIs. Revenue growth then has a chance to exist outside the query as well.