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

Hello friends, 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.

Difference between Equi Join and Non-Equi Join in SQL with Examples

Hello guys, If you want to learn about the difference between Equi and Non-Equi join in SQL then you have come to the right place. Earlier, I have shared the difference between LEFT and RIGHT join in SQL and now we will understand equi and non-equi join in SQL and database. The term "Non-Equi join" in SQL Server may sound abstract, but it's far from it. Equi joins are the same way. I think you'll agree with me after reading this essay. I'll attempt to explain what they are and when they should be used today. So, let's get this party started. 

How to use LEFT, RIGHT, INNER, OUTER, FULL, and SELF JOINs in SQL? Example Tutorial

What are joins and why are they required?

Hello, everyone. Have you ever faced any issues while generating reports or loading data but were using data of just one table was not enough? One table's data is typically insufficient to provide important insights when doing sophisticated analytical processing and data discovery, therefore merging numerous tables is required. SQL, as a method for communicating with relational databases, allows you to create relationships between tables

How to join two tables in a single SQL query? MySQL Example

Hello guys, if you are wondering how to join multiple tables in SQL to produce a combine result which contains columns from all tables but not sure how to do it then you have come to the right place. SQL Join is one of the basic concepts while working in databases or tables but yet less understood and most confusing topic for beginners and even intermediate developers. I compare Joins in SQL  with Recursion in programming in terms of confusion because I have found that these two topics are special in their nature and you can't get it right with casual reading until you understand the concept and its various well.

How to Join Two Unrelated Tables in SQL using Cross Join? Example

 In one of the recent programming job interviews, one of my readers was asked the question, how do you join two tables which are not related to each other? i.e. they don't have any common column? is it possible in SQL? My reader got confused because he only knows about INNER join and OUTER join which require a key column like dept_id which is the primary key in one table like Department and foreign key in another table like Employee. He couldn't answer the question, though he did tell them about you can select data from multiple tables by typing multiple table names in from clause using a comma. 

SQL cross join Example and it's performance

Hello guys, in the past, I gave you a quick overview of SQL JOINS where we learned about different types of JOINS like right, left, inner, and outer join, and in this article, we'll learn about the SQL CROSS JOIN idea in this post, and we'll back up our knowledge with simple examples and illustrations. So, let's start with the basics.

How to join two unrelated tables in SQL? Cross Join Example

 In one of the recent programming job interviews, one of my readers was asked the question, how do you join two tables which are not related to each other? i.e. they don't have any common column? is it possible in SQL? My reader got confused because he only knows about INNER join and OUTER join which require a key column like dept_id which is the primary key in one table like Department and foreign key in another table like Employee. He couldn't answer the question, though he did tell them about you can select data from multiple tables by typing multiple table names in from clause using a comma. 

How to use JOIN Multiple Tables in MySQL? Example Tutorial

 Hello guys, if you are wondering how to join multiple tables in SQL to produce a combine result which contains columns from all tables but not sure how to do it then you have come to the right place. SQL Join is one of the basic concepts while working in databases or tables but yet less understood and most confusing topic for beginners and even intermediate developers. I compare Joins in SQL with Recursion in programming in terms of confusion because I have found that these two topics are special in their nature and you can't get it right with casual reading until you understand the concept and its various well. Things get worse when the table locked due to such SQL Join queries which were fired without knowing how much time it would and how big the result set could be. 

Difference between Self and Equi Join in MySQL

 The main difference between Self Join and Equi Join is that In Self Join we join one table to itself rather than joining two tables. Both Self Join and Equi Join are types of INNER Join in SQL, but there is a subtle difference between the two. Any INNER Join with equal as join predicate is known as Equi Join. SQL Joins are the fundamental concept of SQL similar to correlated and noncorrelated subqueries or using group by clause and a good understanding of various types of SQL join is a must for any programmer.