Description
Write a SQL query to return firstName, lastName, city, state for each person regardless of address. Return all persons even if they don't have an address.
person| Column | Type |
|---|---|
| personId | INT |
| firstName | TEXT |
| lastName | TEXT |
address| Column | Type |
|---|---|
| addressId | INT |
| personId | INT |
| city | TEXT |
| state | TEXT |
Examples
CREATE TABLE person (personId INT, firstName TEXT, lastName TEXT); CREATE TABLE address (addressId INT, personId INT, city TEXT, state TEXT); INSERT INTO person VALUES (1, 'Allen', 'Wang'), (2, 'Bob', 'Smith'); INSERT INTO address VALUES (1, 1, 'New York', 'NY');Allen|Wang|New York|NY
Bob|Smith||Allen (personId=1) has an address in New York, NY. Bob (personId=2) has no address, so city and state are NULL (empty). LEFT JOIN preserves all persons.
CREATE TABLE person (personId INT, firstName TEXT, lastName TEXT); CREATE TABLE address (addressId INT, personId INT, city TEXT, state TEXT); INSERT INTO person VALUES (1, 'John', 'Doe'), (2, 'Jane', 'Smith'), (3, 'Mike', 'Johnson'); INSERT INTO address VALUES (1, 1, 'Los Angeles', 'CA'), (2, 3, 'Chicago', 'IL');John|Doe|Los Angeles|CA
Jane|Smith||
Mike|Johnson|Chicago|ILLEFT JOIN preserves all persons from the person table. John (personId=1) has an address in Los Angeles, Jane (personId=2) has no address so city and state are NULL, and Mike (personId=3) has an address in Chicago.
CREATE TABLE person (personId INT, firstName TEXT, lastName TEXT); CREATE TABLE address (addressId INT, personId INT, city TEXT, state TEXT); INSERT INTO person VALUES (1, 'Sarah', 'Wilson'), (2, 'Tom', 'Brown'); INSERT INTO address VALUES (1, 1, 'Miami', 'FL'), (2, 1, 'Orlando', 'FL');Sarah|Wilson|Miami|FL
Sarah|Wilson|Orlando|FL
Tom|Brown||LEFT JOIN handles one-to-many relationships. Sarah (personId=1) has two addresses, so she appears twice in the result with different city/state combinations. Tom (personId=2) has no address, so he appears once with NULL values for city and state.
Constraints
- •
Use LEFT JOIN