Home
interview questions

How to Join Two Tables using LEFT JOIN in SQL - LeetCode Solution

6

Hello guys, when it comes to combining two tables in SQL, you can use the JOIN clause. Thre are mainly two types of joins, INNER Join, and OUTER join. On Inner join, only those records are selected which has 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 outer join by just changing the position of a table from left to right.

By the way, this is my third article on solving LeetCode SQL problems, earlier I have shown you how to use exists clause to find all thecustomers who have never ordered and how to use the GROUP BY clause tofind 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 which 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 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 Join in SQL then I strongly suggest you first 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 to learn in a more structured way which will eventually help you to solve this kind of problem quickly, both on work and coding interviews.

Combine Two Tables using LEFT JOIN in SQL - LeetCode Solution

As I told you, we can solve this problem by joining both 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 if there is an address or not.

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

In this example, we will use the LEFT JOIN 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.

How to combine Two Tables using LEFT JOIN in SQL - LeetCode Solution

That's all about how to combine tables in SQL using LEFT and RIGHT Outer Joins. Just remember, if you need only matching records from both tables, then use INNER Join, if you need all records from one table in addition to matching records from another table then 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 JOUN make sure you put the right table on the left side of the JOIN clause like the table from which you need all records.

Further Learning

The Complete SQL Bootcamp

Introduction to SQL

SQL for Newbs: Data Analysis for Beginners

SQL Puzzles and Answers, the 2nd Edition

SQL for Data Science on Coursera

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)
  • Difference between Primary and Candidate key in table? (answer)
  • 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
  • Difference between Unique and Primary key in table? (answer)
  • Top 5 Courses to learn PostgreSQL in-depth (courses)
  • 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
  • Difference between Primary and Foreign key in 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, then you can also take a look at the Introduction to Databases and SQL Querying free course on Udemy to kick-start your learning.