Combine Two Tables

Write an SQL query to join Person and Address tables and retrieve personal information with corresponding address details.

Understand the Problem

Problem Statement

Write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.

Table: Address

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| addressId   | int     |
| personId    | int     |
| city        | varchar |
| state       | varchar |
+-------------+---------+
addressId is the primary key column for this table.
Each row of this table contains information about the city and state of one person with ID = PersonId.

Return the result table in any order.

Constraints

The Person table contains unique personId values as primary keys
The Address table contains addressId as primary key and personId as foreign key
Each person can have at most one address record
Column names in output must be: firstName, lastName, city, state (case-sensitive)
Output should include all persons, even those without address information
Null values should be returned for missing address data

Examples

Example 1

Input

Person table:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Wang     | Allen     |
| 2        | Alice    | Bob       |
+----------+----------+-----------+
Address table:
+-----------+----------+---------------+------------+
| addressId | personId | city          | state      |
+-----------+----------+---------------+------------+
| 1         | 2        | New York City | New York   |
| 2         | 3        | Leetcode      | California |
+-----------+----------+---------------+------------+

Output

+-----------+----------+---------------+----------+
| firstName | lastName | city          | state    |
+-----------+----------+---------------+----------+
| Allen     | Wang     | Null          | Null     |
| Bob       | Alice    | New York City | New York |
+-----------+----------+---------------+----------+

Explanation

The query returns all persons from the Person table. For personId=1 (Allen Wang), there is no corresponding address record, so null values are shown for city and state. For personId=2 (Bob Alice), there is a matching address record with city 'New York City' and state 'New York'.

Example 2

Input

Person table:
+----------+----------+-----------+
| personId | lastName | firstName |
+----------+----------+-----------+
| 1        | Smith    | John        |
| 2        | Doe      | Jane        |
+----------+----------+-----------+
Address table:
+-----------+----------+----------+-------+
| addressId | personId | city     | state |
+-----------+----------+----------+-------+
| 1         | 1        | Boston   | MA    |
+-----------+----------+----------+-------+

Output

+-----------+----------+----------+-------+
| firstName | lastName | city     | state |
+-----------+----------+----------+-------+
| John      | Smith    | Boston   | MA    |
| Jane      | Doe      | Null     | Null  |
+-----------+----------+----------+-------+

Explanation

John Smith has an address record, so his city and state are populated. Jane Doe has no address record, so null values appear for city and state columns.

Solution

/*
 * Note: This is a SQL query, not C code.
 * However, if implementing this logic in C with a database interface:
 */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

// This would be the SQL query executed via database API
const char* sql_query = "SELECT p.firstName, p.lastName, a.city, a.state "
                         "FROM Person p "
                         "LEFT JOIN Address a ON p.personId = a.personId";

int main() {
    printf("SQL Query to execute:\n");
    printf("%s\n", sql_query);
    printf("\nThis query performs a LEFT JOIN to combine Person and Address tables.\n");
    printf("All persons are included, with null values for missing addresses.\n");
    
    return 0;
}

Time:O(n + m)

Space:O(1)

Approach:

Since this is fundamentally an SQL problem, the C solution demonstrates how the SQL query would be constructed and executed in a C program using a database interface. The core logic remains the same - using a LEFT JOIN operation.

The SQL query string shows the exact syntax needed to solve the problem. In a real application, this would be executed through a database API like MySQL Connector/C or SQLite.

Visual Explanation

Loading diagram...

All Problems