DEV Community

Cover image for Combine Two Tables
Oluwanifemi Latunde
Oluwanifemi Latunde

Posted on

Combine Two Tables

Day 8 of the #I4G10DaysOfCodeChallenge featured combining two tables in SQL. You can read more about the problem here.

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.
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

Question:
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.

Solution:

When combining rows from two or more tables based on a shared column, a JOIN clause is utilised.
There are different kind of JOINS in SQL and you can read more about it in the w3schools Tutorial on SQL.

Because we are not exactly fixated on finding matching values in the table we can use either a LEFT or RIGHT JOIN depending on the sequence in which you write your SQL query.

The LEFT JOIN keyword returns all records from the left table (Person) , and the matching records from the right table (Address) . The result is 0 records from the right side, if there is no match.

The RIGHT JOIN keyword returns all records from the right table (Person), and the matching records from the left table (Address). The result is 0 records from the left side, if there is no match.

Thank You🫶🏼

Top comments (0)