Combine Two Tables
Problem
Write a SQL query for a report that provides the firstName, lastName, city, and state of each person in Person. If the address of a personId is not present in Address, report NULL.
SQL: SELECT firstName, lastName, city, state FROM Person LEFT JOIN Address ON Person.personId = Address.personId;
[{1, "Wang", "Allen"}, {2, "Alice", "Bob"}][{1, 2, "NY", "NY"}][{"Allen","Wang","NY","NY"}, {"Bob","Alice",NULL,NULL}]SELECT firstName, lastName, city, state
FROM Person
LEFT JOIN Address ON Person.personId = Address.personId;Explanation
The report must list every person, even those with no address on file. That single requirement is what tells us to use a LEFT JOIN rather than a plain inner join.
A normal join only keeps rows that match on both sides. But a LEFT JOIN keeps all rows from the left table (here Person) and pulls in matching Address columns where they exist.
The matching happens on Person.personId = Address.personId. When a person has a matching address, their city and state are filled in. When there is no match, those columns automatically come back as NULL.
Example: Person has Allen (id 1) and Bob (id 2), but Address only has a row for id 1. Allen gets his city and state; Bob still appears, with NULL for city and state.
The database typically builds a lookup on the address side keyed by personId, so each person is matched in roughly constant time, giving a single efficient pass.