#16EasySQL· Outer Joins

Combine Two Tables

A directory stores people and their postal addresses in two tables. **Person** | Column | Type | |---|---| | personId | INT (primary key) | | lastName | VARCHAR | | firstName | VARCHAR | **Address** | Column | Type | |---|---| | addressId | INT (primary key) | | personId | INT | | city | VARCHAR | | state | VARCHAR | `Address.personId` refers to a person, but some people have no address and some addresses may refer to a `personId` that is not in `Person`. Each person has at most one address. Write a query that lists **every** person with columns `firstName`, `lastName`, `city`, `state`. When a person has no address, `city` and `state` must be `NULL`. Addresses that belong to no listed person must not appear. Order the result by `personId` ascending.

Input format

Person rows as [personId, lastName, firstName]; Address rows as [addressId, personId, city, state]

Output format

Result rows as [firstName, lastName, city, state], ordered by personId; `null` represents SQL NULL

Examples

Input: Person = [[1, "Okafor", "Chidi"], [2, "Lindqvist", "Maja"]], Address = [[1, 2, "Uppsala", "Uppsala County"], [2, 9, "Porto", "Norte"]]
Output: [["Chidi", "Okafor", null, null], ["Maja", "Lindqvist", "Uppsala", "Uppsala County"]]
Chidi has no address, so his city and state are NULL. Maja's address is attached. The address for personId 9 is ignored.
Input: Person = [[3, "Novak", "Petra"], [1, "Haddad", "Omar"]], Address = [[10, 1, "Beirut", "Beirut"], [11, 3, "Brno", "South Moravia"]]
Output: [["Omar", "Haddad", "Beirut", "Beirut"], ["Petra", "Novak", "Brno", "South Moravia"]]
Rows come back ordered by personId, not in insertion order.

Constraints

  • 0 <= rows in Person, Address <= 10^4
  • personId is unique in Person and appears at most once in Address.

Study first

  • #sql
  • #database
  • #left-join