SQL JOINs Explained with Practical Examples

Per the official PostgreSQL Table Expressions Documentation, SQL JOIN clauses are the bedrock of relational database analysis, allowing you to create a single, coherent view from multiple, normalized tables. Mastering how each join type includes or excludes rows is essential for accurate reporting, from calculating customer order totals to identifying orphaned records in a financial audit.

This guide provides a practical, no-fluff walkthrough of the four primary SQL JOINs, using a simple, reproducible dataset to demonstrate exactly how each one works.

Practice Dataset Preview

Our illustrative dataset contains two tables: customers with 4 unique customers and orders with 5 total orders. Notice that customer 'David' has no orders, and order 'O105' is a guest order with no matching customer.

customers (4 Rows)

orders (5 Rows)

INNER JOIN: Matching Rows Only

An INNER JOIN returns only the rows where the join key exists in both tables. It effectively filters out any records that do not have a match.

SELECT
  c.customer_name,
  o.order_id,
  o.order_total
FROM customers c
INNER JOIN orders o
  ON c.customer_id = o.customer_id;

Result (4 Rows): An INNER JOIN on these tables returns 4 rows, showing only customers who have placed orders. It excludes customer 'David' (no orders) and guest order 'O105' (no matching customer).

LEFT JOIN: All Rows from the Left Table

A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table (customers) and any matching rows from the right table (orders). If a left-table row has no match, the right-side columns are filled with NULL.

SELECT
  c.customer_name,
  o.order_id,
  o.order_total
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id;

Result (5 Rows): A LEFT JOIN returns 5 rows, including the customer 'David' who has no matching orders. The guest order 'O105' is still excluded because its key (C5) is not in the left customers table.

RIGHT JOIN: All Rows from the Right Table

A RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of a LEFT JOIN. It returns all rows from the right table (orders) and any matching rows from the left table (customers).

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

Result (5 Rows): A RIGHT JOIN also returns 5 rows, but includes the guest order 'O105' which has no matching customer. Customer 'David' is now excluded because he has no record in the right orders table.

FULL OUTER JOIN: All Rows from Both Tables

A FULL OUTER JOIN combines the results of both LEFT and RIGHT joins. It returns all rows from both tables, filling in NULLs on either side where matches do not exist. This is most useful for data reconciliation and auditing.

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

Result (6 Rows): A FULL OUTER JOIN returns all 6 unique rows from both tables, showing all customers and all orders. Both 'David' and the guest order 'O105' are included, with NULL values in their non-matching columns.

JOIN Types Compared

Common Pitfall: The Deadly JOIN Fan-Out Trap

A frequent and dangerous error occurs when you join tables with a one-to-many relationship. This can cause rows from the "one" side to be duplicated, leading to massively inflated aggregate calculations.

Let's introduce a third table, customer_spend_limits, which has a one-to-one relationship with customers.

customer_spend_limits

Now, consider a query that joins all three tables to sum the total credit limit.

-- TRAP QUERY: Incorrectly sums the duplicated credit_limit
SELECT
  SUM(sl.credit_limit) AS inflated_total_credit
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN customer_spend_limits sl ON c.customer_id = sl.customer_id;

Because customer 'Bob' (C2) has two orders, his credit_limit of 500 is duplicated in the joined result set. The query calculates 1000 (Alice) + 500 (Bob) + 500 (Bob again) = 2000, which is wrong.

The Fix: Aggregate Before Joining To prevent this, aggregate your measures to the correct grain before you perform the join.

-- FIX QUERY: Aggregates metrics before the join
WITH customer_orders AS (
  SELECT customer_id FROM orders
)
SELECT
  SUM(sl.credit_limit) AS correct_total_credit
FROM customer_spend_limits sl
JOIN (SELECT DISTINCT customer_id FROM customer_orders) co
  ON sl.customer_id = co.customer_id;

This approach ensures each customer's credit limit is counted only once. The incorrect query inflates the total credit limit to an illustrative $2,000, while the correct total is only $1,500.

The Anti-Join Pattern: Finding Unmatched Rows

A common analytical task is to find records in one table that have no corresponding records in another. For example, which customers have never placed an order? This is called an "anti-join".

The most reliable way to perform an anti-join is with a LEFT JOIN and a WHERE ... IS NULL clause.

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

Result (1 Row): This query correctly identifies 'David' as the only customer who does not appear in the orders table.

Interactive Practice Challenge: Test Your Query

Open sql-joins-practice.db in sqliteonline.com and test your understanding with this real-world prompt:

Business Challenge: Write a query that returns each customer's name, total order count, and total spend across all orders. Customers with zero orders must be included with an order count of 0 and a total spend of 0.

Expected Output

SELECT 
  c.customer_name,
  COUNT(o.order_id) AS order_count,
  COALESCE(SUM(o.order_total), 0) AS total_spend
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_spend DESC;