How to Join Two or More Tables in a SQL query? Left Join Example Leetcode Solution

Hello guys, when it comes to combining two tables in SQL, many programmers don't know that they can use the JOIN clause. In fact, JOIN is there to fetch data from multiple tables together. There are mainly two types of joins, INNER Join and OUTER join. On Inner join, only those records have matching values in both tables, while in Outer join, all records from one table are selected in addition to matching records from other tables. There are two kinds of Outer join in SQL, LEFT OUTER JOIN and RIGHT OUTER JOIN. Both are actually the same thing, which means you can get the same result by using either of the outer joins by changing the table's position from left to right.

This is my third article on solving LeetCode SQL problems; earlier, I have shown you how to use the existing clause to find all the customers who have never ordered and how to use the GROUP BY clause to find the duplicate emails from the table. If you need some practice, you can check those articles as well.

LeetCode also has a good collection of SQL problems that are good to improve your SQL query skills, and I suggest you take a look at those problems if you want to improve your SQL query skills. Now let's come to the LeedCode problem; there are two tables, Person and Address, as shown below :

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.


Problem - 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

You can solve this problem using LEFT or RIGHT outer join because you need to combine two tables here to get both Names and Address related information. If you are not familiar with joining SQL, I strongly suggest you start with a comprehensive SQL course like The Complete SQL Bootcamp course by Jose Portilla on Udemy.

SQL Joins is one of the tricky but essential concepts to learn, and going through this course will help you learn in a more structured way, which will eventually help you solve this kind of problem quickly, both during work and in coding interviews.

How to Combine Rows from two or  more tables using LEFT JOIN in SQL?

As I told you, we can solve this problem by joining both the Person and Address table. You can use either Inner Join or Outer join to combine the table, but the key thing to note here is that you need to print records for each person in the Person table, regardless of whether there is an address.

This means you cannot solve this problem using INNER join because if you used INNER join, only persons with addresses would be printed. If we need to print all the persons with or without addresses, we need to use the  LEFT JOIN (Person LEFT JOIN Address) or a RIGHT JOIN (Address RIGHT JOIN Person).

We will use the LEFT JOIN in this example because that is much easier to read.

Here is the solution to this SQL problem :

# WRITE your MySQL query statement below
 
SELECT FirstName, LastName, City, State
 FROM Person p 
 LEFT JOIN Address a 
 ON p.PersonId = a.PersonId

You can also write this query as see the OUTER word; this is optional in MySQL, which means both LEFT JOIN and LEFT OUTER JOIN will work. You can see The Ultimate MySQL Bootcamp course on Udemy to learn more about how SQL works inside the MySQL database.

# WRITE your MySQL query statement below
 
SELECT FirstName, LastName, City, State 
   FROM Person p 
   LEFT OUTER JOIN Address a 
   ON p.PersonId = a.PersonId

The same query can be written using the RIGHT OUTER JOIN as well. Here is how you do it :

# WRITE your MySQL query statement below
 
SELECT FirstName, LastName, City, State 
    FROM Address a 
    RIGHT OUTER JOIN Person p
    ON a.PersonId = p.PersonId

This query will produce the same output as the above two queries. If you look carefully, we have exchanged the position of Person and Address table in this query. Earlier Person was on the left-hand side, but now because of RIGHT JOIN, it's on the right side.

Joins are trick and there are so many of them to learn, but this diagram and SQL for Data Science course on Coursera is a good way to learn them after all SQL is one of the essential skills for both Programmers and Data Scientist.

That's all about how to combine data from multiple tables in SQL using LEFT and RIGHT Outer Joins. If you need only matching records from both tables, then use INNER Join; if you need all records from one table and matching records from another table, please use OUTER JOIN in SQL.

You are free to use LEFT or RIGHT joins as per your liking, but if you use LEFT OUTER JOIN make sure you put the right table on the left side of the JOIN clause, like the table from which you need all records.


Other related SQL queries, Interview questions, and articles:

• How to join three tables in one single SQL query (solution)
• 10 Frequently asked SQL Query interview questions (solution)
• Write SQL Query to find duplicate emails - LeetCode problem (solution)
• Write a SQL query to find all table names on a database in MySQL (solution)
• 5 Courses to learn Database and SQL Better (courses)
• Top 5 Websites to learn SQL online for FREE? (resource)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Write a SQL query to copy or backup a table in MySQL (solution)
• How do you find the duplicate rows in a table on a database? (solution)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• 5 Free Courses to learn Database and SQL (free courses)
• How to migrate SQL queries from Oracle to SQL Server 2008? (answer)
• 4 Free Books to learn Microsoft SQL Server database (books)
• Top 5 Websites to learn SQL online for FREE? (websites)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• What is the difference between View and Materialized View in Database? (answer)
• Difference between clustered and non-clustered index in SQL? (answer)
• 5 Advanced SQL books to level up your SQL skills (books)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• Difference between Self and Equi Join in SQL? (answer)
• What difference between the Primary and Candidate key in table? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between the Unique and Primary keys in the table? (answer)
• Top 5 Courses to learn PostgreSQL in-depth (courses)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• What difference between the Primary and Foreign keys in the table? (answer)
• Top 5 Courses to learn Microsoft SQL Server in-depth (courses)

Thanks for reading this article, if you like this SQL article, then please share with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P.S. - If you are interested in learning Database and SQL and looking for some free resources to start your journey, you can also look at the Introduction to Databases and SQL Querying free course on Udemy to kick-start your learning.