Combine Two Tables - Problem
🔗 Database Table Join Challenge
You're working with a company's employee database that stores personal information and addresses in separate tables. Your task is to create a comprehensive report that combines both pieces of information.
Given two tables:
- Person Table: Contains employee IDs, first names, and last names
- Address Table: Contains address information linked to person IDs
Write a LEFT JOIN query to retrieve all employees along with their address information. If an employee doesn't have an address record, show NULL for the city and state fields.
Goal: Return firstName, lastName, city, and state for every person in the Person table, regardless of whether they have address information.
Input & Output
example_1.sql — Basic LEFT JOIN
$
Input:
Person table: [{personId: 1, lastName: 'Wang', firstName: 'Allen'}, {personId: 2, lastName: 'Bob', firstName: 'Bob'}]\nAddress table: [{addressId: 1, personId: 1, city: 'New York City', state: 'New York'}]
›
Output:
[{firstName: 'Allen', lastName: 'Wang', city: 'New York City', state: 'New York'}, {firstName: 'Bob', lastName: 'Bob', city: null, state: null}]
💡 Note:
Person 1 (Allen Wang) has a matching address record, so their city and state are included. Person 2 (Bob Bob) has no address record, so city and state are NULL.
example_2.sql — All Persons Have Addresses
$
Input:
Person table: [{personId: 1, lastName: 'Smith', firstName: 'John'}, {personId: 2, lastName: 'Doe', firstName: 'Jane'}]\nAddress table: [{addressId: 1, personId: 1, city: 'Los Angeles', state: 'California'}, {addressId: 2, personId: 2, city: 'Miami', state: 'Florida'}]
›
Output:
[{firstName: 'John', lastName: 'Smith', city: 'Los Angeles', state: 'California'}, {firstName: 'Jane', lastName: 'Doe', city: 'Miami', state: 'Florida'}]
💡 Note:
Both persons have matching address records, so all city and state fields are populated with real values.
example_3.sql — Empty Address Table
$
Input:
Person table: [{personId: 1, lastName: 'Johnson', firstName: 'Mike'}]\nAddress table: []
›
Output:
[{firstName: 'Mike', lastName: 'Johnson', city: null, state: null}]
💡 Note:
Edge case where Address table is empty. All persons are still returned with NULL values for city and state fields.
Visualization
Tap to expand
Understanding the Visualization
1
Start with Person Table
The Person table is our 'left' table - we want to keep ALL records from here
2
Match with Address Table
For each person, try to find their address using personId as the key
3
Handle Missing Matches
If no address is found for a person, include them anyway with NULL address fields
4
Generate Result
Final result contains all persons with their address info (or NULL if no address)
Key Takeaway
🎯 Key Insight: LEFT JOIN is like creating a comprehensive employee directory - you include everyone from your employee list (left table), and add their address information where available. If someone hasn't provided their address yet, you still include them in the directory with blank address fields (NULL values). This ensures no one gets excluded from the final report!
Time & Space Complexity
Time Complexity
O(n + m)
Linear time with proper indexing on personId columns
✓ Linear Growth
Space Complexity
O(n)
Output size proportional to Person table size
⚡ Linearithmic Space
Constraints
- 1 ≤ Person table size ≤ 1000
- 0 ≤ Address table size ≤ 1000
- personId is unique in Person table (primary key)
- addressId is unique in Address table (primary key)
- personId in Address table may reference non-existent Person records
- firstName and lastName are non-null varchar fields
- city and state are varchar fields that can be null in result
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code