Explain SQL joins code

What this SQL joins sample shows

JOIN and LEFT JOIN

JOIN keeps only the rows that match on both sides: query 1 returned 3 rows, Ana twice and Ben once. LEFT JOIN keeps every row of the left table and fills the right side with NULL where nothing matches: query 2 returned 4 rows, the same 3 plus Cy with an empty total.

rows with no match

Query 3 is a LEFT JOIN that keeps only the rows where the right side came back empty, WHERE o.id IS NULL, and it returned Cy, the one customer with no orders. The same question written with NOT EXISTS and a subquery also returned Cy.

the doubled total

Query 4 joins orders and refunds to the same customer at once. Ana has 2 orders and 2 refunds, so the join made 4 rows for her, each order paired with each refund, and both sums counted everything twice: spent came out as 100.0 and refunded as 20.0, against 50 and 10. Ben has no refunds, so his refunded came back empty, NULL, not 0.

WHERE after a LEFT JOIN

A condition like o.total > 25 on the right-hand table in WHERE throws away the rows where that side came back NULL, so the LEFT JOIN acts like a plain JOIN. With WHERE o.total > 25 added to query 2, only Ana's 30.0 came back. Moved into the join itself, ON o.customer_id = c.id AND o.total > 25, Ana's 30.0 came back with Ben and Cy, both with an empty total.

Good to know

• Run with SQLite 3.53.4 on a fresh database built from the 3 tables in the comment. JOIN and LEFT JOIN work the same way in PostgreSQL and MySQL. SQLite has accepted FULL JOIN since version 3.39, and here a FULL JOIN of orders and customers returned 4 rows; MySQL has no FULL JOIN.
• Query 2 has no ORDER BY, so the database can return its rows in any order: it listed Ana's 20.0 before her 30.0, the other way round from query 1.

Watch out: A total that is too big, with no error, can come from the join, not the data. Add up each table on its own first, then join the results: with orders and refunds each summed per customer in a subquery, Ana came back as 50.0 spent and 10.0 refunded. Running the joined rows without SUM and GROUP BY shows the repeats: 4 rows for Ana.