How does a LEFT JOIN keep the rows that have no match, and what does it put in the other table's columns?
A left join keeps every row of the table on the left, and it puts a missing value in the columns of the table on the right for the rows that found no partner.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO customers (id, name) VALUES
(1, 'Ada Lovelace'),
(2, 'Grace Hopper'),
(3, 'Alan Turing');
INSERT INTO orders (id, customer_id, amount) VALUES
(1, 1, 250.00),
(2, 1, 120.00),
(3, 2, 900.00);
-- Every customer, and their orders where they have any.
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name, o.amount;
name amount
Ada Lovelace 120.00
Ada Lovelace 250.00
Alan Turing (missing)
Grace Hopper 900.00
So what is in the answer for Alan Turing? His name is there, and the amount is missing. The row is still in the answer, and the columns of the table on the right are all missing for it.
That missing value is the thing to be careful with. A function that adds up the column ignores it, so the total for a customer with no orders is a missing value rather than a zero. And if you put it into a calculation, the answer is missing as well.
-- The count of the real orders, and a total that is zero.
SELECT
c.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;
The next question in this topic is about the mistake that everybody makes with this join.