SQL implementation of LeetCode (175. Joining two tables)

[LeetCode] 175.Combine Two Tables

Table: Person

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key column for this table.

Table: Address

+-------------+---------+
| Column Name | Type |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId is the primary key column for this table.

Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:

FirstName, LastName, City, State

LeetCode also has a question about database. Let's do it. This is the first question. It is relatively simple. It is a question of joint search between two tables. We need to use Join operation. For some Join operations, you can read my previous blog SQL Left Join, Right Join, Inner Join, and Natural Join. The most direct way is to use Left Join to join the two tables according to PersonId:

Solution 1:

SELECT Person.FirstName, Person.LastName, Address.City, Address.State FROM Person LEFT JOIN Address ON Person.PersonId = Address.PersonId;

When using Left Join, we can also use the Using keyword to declare which column name we want to use for the join:

Solution 2:

SELECT Person.FirstName, Person.LastName, Address.City, Address.State FROM Person LEFT JOIN Address USING(PersonId);

Or we can add the Natural keyword so that we don't have to declare specific columns and MySQL can search for the same columns by itself:

Solution 3:

SELECT Person.FirstName, Person.LastName, Address.City, Address.State FROM Person NATURAL LEFT JOIN Address;

References:

https://leetcode.com/discuss/21216/its-a-simple-question-of-left-join-my-solution-attached

https://leetcode.com/discuss/53001/comparative-solution-between-left-using-natural-left-join

This is the end of this article about SQL implementation of LeetCode (175. Join two tables). For more relevant SQL implementation of joining two tables, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!