Day 8 of 10: Ingressive For Good Data Structures and Algorithm
Combining two tables using SQL
I broke a record today! I completed the challenge in less than ten minutes and on the first trial, too!
And that's because today is sql day.
SQL is a language used to communicate with databases. It is quite simple due to its easy to understand syntax.
Today's task required extracting information from two tables.
Instructions
Table: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| personId | int |
| lastName | varchar |
| firstName | varchar |
+-------------+---------+
personId is the primary key column for this table. This table contains information about the ID of some persons and their first and last names.
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.
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.
Return the result table in any order.
Testcase
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:
There is no address in the address table for the personId = 1 so we return null in their city and state. addressId = 1 contains information about the address of personId = 2.
My Solution
I used the SELECT keyword to select the columns first name, and last name FROM the Persons table and the columns city, and state from the Address table. Then, I performed a LEFT OUTER JOIN on the two tables ON the primary key, personId, common to both tables.
The LEFT OUTER JOIN by default returns all columns from the left table, Persons and null if there are no matches of the PersonId from the left table in the right table, Address.
And that's all it took!
Surprisingly, the memory usage as seen above is 0b. I tried googling reasons to explain why that is so but i got no concrete answer.
Do you know why? Kindly drop a comment if you do. Thanks.
Have a beautiful day!