Pepelen

Learner can join two small tables with INNER JOIN and LEFT JOIN and can predict which rows (and which NULLs) appear.

1 / 5

Keys, INNER JOIN, and LEFT JOIN

Linking two tables and handling missing matches

Real databases store data in separate tables linked by keys. A key is a column whose value in one table matches a column in another. The ON clause tells SQL which columns to match. INNER JOIN returns only the rows that have a match in both tables. If a row in the left table has no matching row in the right table (or vice versa), it is dropped entirely from the result. LEFT JOIN (also written LEFT OUTER JOIN) returns every row from the left table. For rows that have a match in the right table, the right-side columns are filled in normally. For rows with no match, the right-side columns are filled with NULL. Example — two tables: customers orders id | name order_id | customer_id | amount ---+------ ---------+-------------+------- 1 | Alice 1 | 1 | 80 2 | Bob 2 | 1 | 40 3 | Carol 3 | 2 | 60 INNER JOIN on customers.id = orders.customer_id: name | amount ------+------- Alice | 80 Alice | 40 Bob | 60 (Carol has no orders — she is excluded) LEFT JOIN on the same condition: name | amount ------+------- Alice | 80 Alice | 40 Bob | 60 Carol | NULL (Carol appears, amount is NULL because she has no matching order) A common beginner mistake: expecting INNER JOIN to keep all customers including those with no orders. It does not — use LEFT JOIN for that.
Lesson notes
Linking two tables and handling missing matches
Real databases store data in separate tables linked by keys. A key is a column whose value in one table matches a column in another. The ON clause tells SQL which columns to match. INNER JOIN returns only the rows that have a match in both tables. If a row in the left table has no matching row in the right table (or vice versa), it is dropped entirely from the result. LEFT JOIN (also written LEFT OUTER JOIN) returns every row from the left table. For rows that have a match in the right table, the right-side columns are filled in normally. For rows with no match, the right-side columns are filled with NULL. Example — two tables: customers orders id | name order_id | customer_id | amount ---+------ ---------+-------------+------- 1 | Alice 1 | 1 | 80 2 | Bob 2 | 1 | 40 3 | Carol 3 | 2 | 60 INNER JOIN on customers.id = orders.customer_id: name | amount ------+------- Alice | 80 Alice | 40 Bob | 60 (Carol has no orders — she is excluded) LEFT JOIN on the same condition: name | amount ------+------- Alice | 80 Alice | 40 Bob | 60 Carol | NULL (Carol appears, amount is NULL because she has no matching order) A common beginner mistake: expecting INNER JOIN to keep all customers including those with no orders. It does not — use LEFT JOIN for that.
Joining tables: INNER and LEFT JOIN — Data Analytics from Scratch: SQL, Spreadsheets and Metrics