Joins

INNER versus LEFT

An inner join returns rows that have matches on both sides. A left join returns every row from the left table, with NULLs where the right side has no match, which is how you include customers with zero orders. Join on keys (foreign key to primary key), and be careful when joining one-to-many relationships before aggregating: rows multiply, so aggregate at the right level or use count(DISTINCT ...).

Inner join versus left join, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. The inner join lists the 5 orders with customer names; John has none so he does not appear. The left join with count(o.id) includes John with 0 orders.

-- inner join: only customers with orders
SELECT c.name, o.id AS order_id, o.status
FROM customers c
JOIN orders o ON o.customer_id = c.id
ORDER BY o.id;

-- left join: every customer, even without orders
SELECT c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY c.name;

Output:

 name  | order_id |  status   
-------+----------+-----------
 Asha  |        1 | paid
 Asha  |        2 | shipped
 Ravi  |        3 | paid
 Meera |        4 | new
 Ravi  |        5 | cancelled
(5 rows)

 name  | orders 
-------+--------
 Asha  |      2
 John  |      0
 Meera |      1
 Ravi  |      2
(4 rows)

Count the right column

In a left join, count(o.id) counts matches; count(*) would count 1 for customers without orders.

Quick check: Which join keeps customers who have no orders?

• CROSS JOIN
• INNER JOIN
• LEFT JOIN from customers to orders
• A WHERE filter on orders