A SQL JOIN combines rows from two data sources using a relationship you specify. The key question is not simply “Which JOIN is faster?” but which rows must the result preserve and how many matches each row can have. A JOIN with the wrong key or filter can quietly lose customers, duplicate invoice amounts or report incorrect counts even when it runs without errors.

These examples use PostgreSQL-compatible SQL. SQL Server supports the core INNER, LEFT, RIGHT, FULL and CROSS JOIN concepts, although some syntax and execution-plan tools differ. An explicit JOIN ... ON is clearer than relying on comma-separated table lists and implicit join predicates.

Example tables and expected rows

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name VARCHAR(40)
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount NUMERIC(10,2)
);
INSERT INTO customers VALUES (1,'Amira'),(2,'Ben'),(3,'Chen');
INSERT INTO orders VALUES (101,1,30),(102,1,50),(103,2,20),
                          (104,99,10);

The deliberately inconsistent sample has three customers, four orders, two orders for Amira, one for Ben, none for Chen and an order referencing nonexistent customer 99. The last record is useful for demonstrating unmatched rows; in a properly constrained live database, a valid foreign-key constraint would normally prevent this record from being inserted.

INNER JOIN: keep only matching pairs

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

Three rows result: Amira order 101, Amira order 102, and Ben order 103. Chen has no match; orphan order 104 is also excluded. One-to-many relationships mean a customer can appear more than once, so a JOIN does not necessarily produce one row per customer.

LEFT JOIN: preserve all left-side rows

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

This returns four rows: two for Amira, one for Ben, and one for Chen with NULL order fields. A LEFT JOIN does not automatically create zeros; NULL in an unmatched field signifies the absence of a matching row. To display a numeric default, COALESCE can be used deliberately after considering what “no activity” means.

RIGHT JOIN and FULL OUTER JOIN

A RIGHT JOIN preserves all rows of its right input and NULL-fills fields from a nonmatching left input. Reversing table order often produces a more readable LEFT JOIN with the same intent. For the example below, order 104 remains, with missing customer details:

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

A FULL OUTER JOIN preserves unmatched records from both sides. With the sample data it returns five rows: three matched order rows, Chen with no order, and order 104 with no customer.

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

PostgreSQL and SQL Server support FULL OUTER JOIN. Not every database system supports it directly; if emulating it with two outer joins, account for duplicates with an unmatched-row predicate rather than blindly combining two large results with UNION.

Find missing relationships with an anti-join

Use a LEFT JOIN and a NULL test on the right table’s non-nullable key to find customers with no orders:

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

Only Chen is returned. An alternative is NOT EXISTS, which often expresses the requirement clearly and avoids duplicated left rows:

SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

To find orphaned orders, preserve orders on the left and test c.customer_id IS NULL after joining customers. Be cautious with NOT IN when its subquery can contain NULL values, because SQL’s three-valued logic can change the result.

ON versus WHERE changes an outer JOIN

The position of a filter matters in an outer JOIN. To keep all customers while matching only large orders, place the condition in ON:

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.amount >= 40;

Amira has order 102, Ben is preserved with NULL order, and Chen is also preserved with NULL order. In contrast, moving o.amount >= 40 to the WHERE clause removes those NULL-extended rows and behaves like an inner result for that condition. Filter placement is part of business correctness, not just style.

CROSS JOIN and NATURAL JOIN

CROSS JOIN creates every combination of input rows. Three customers crossed with four orders create 12 rows. This can be legitimate for a coverage matrix but accidentally expensive on large tables. Prefer explicit CROSS JOIN rather than a comma-separated FROM that disguises the intent.

NATURAL JOIN implicitly joins on all shared column names, not merely names that happen to have “compatible data types.” If a future schema change introduces another shared column, the join semantics can unexpectedly change; if there are no common names, it can behave as a CROSS JOIN. Use explicit ON or USING for maintainable production queries.

SELF JOIN and composite keys

A SELF JOIN uses a table more than once with different aliases. For an employees(employee_id, manager_id, name) table, a self-join can show supervisors without losing employees whose manager is NULL:

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

For composite relationships, join all participating key columns, such as ON a.tenant_id=b.tenant_id AND a.order_id=b.order_id. Joining only order_id when it is reused across tenants can mix different customers’ data—a correctness and privacy problem.

Aggregate after JOIN without multiplying totals

SELECT c.customer_id, c.name,
       COUNT(o.order_id) AS order_count,
       COALESCE(SUM(o.amount),0) AS total_amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;

For the sample: Amira has two orders totaling 80, Ben one totaling 20, Chen zero totaling zero. COUNT(o.order_id) is used instead of COUNT(*) so Chen’s single NULL-extended joined row does not count as an order. If you join another one-to-many table (such as order line items), pre-aggregate at the required grain before summing order-level amounts.

Find a customer’s latest order without ambiguous ties

Using MAX(order_date) by customer finds the most recent date, but if two orders share that date a subsequent join may return multiple rows. A portable window ranking pattern can make the tie-breaking explicit:

WITH ranked AS (
  SELECT order_id, customer_id, order_date,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS position
  FROM orders_with_dates
)
SELECT customer_id, order_id, order_date
FROM ranked WHERE position = 1;

orders_with_dates here is an illustrative extension with an order_date field, not the five-row dataset created above. Use RANK instead if tied latest orders all belong in the result. Avoid examples containing undefined aliases such as JOIN Orders ON ... while later referring to O.

CTEs, temp tables and optimisation

A common table expression (CTE) can make a query easier to understand, but adding a CTE does not guarantee materialisation or improved speed. Depending on the engine and version, the optimiser may inline or materialise it. Temporary tables can be useful for measured cases involving repeated intermediate results, but add write and maintenance overhead.

Logical JOIN order and actual physical join order can differ because optimizers may reorder inner joins. Physical strategies include nested loops, hash joins and merge joins, each suitable under particular cardinalities, data distributions and indexes. A right logical join is not necessarily more expensive than an equivalent left join simply because of its label.

Inspect EXPLAIN or the target database execution plan for estimates versus actual rows, scans, index access, build/probe sides, memory spills and row multiplication. An index can help selected joins, but always indexing every foreign key and forcing broadcast join hints is not universally correct. Measure on representative data before keeping a tuning change.

Practical JOIN error checklist

Try these exercises

1. Return each customer, including Chen, and count their orders. Confirm totals 2, 1 and 0.
2. Identify the orphan order, and explain why an enforced foreign key would prevent it.
3. Compare a LEFT JOIN with a right-table filter in ON versus WHERE, and explain which customers disappear.
4. Create a second one-to-many dimension and demonstrate how failing to pre-aggregate duplicates sales totals.

JOINs are useful when they reflect the data model: which rows should match, which must be preserved, and how many rows the final result should contain. Validate those assumptions on small known data before applying the same query to large operational systems.