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.

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. 

When to use TRUNCATE vs DELETE command in SQL?

 While working with the database we often need to delete data, sometimes to create more space, sometimes just remove all data to make the table ready for the next day's transaction, or sometimes just selectively remove stale data. SQL provides a couple of handy commands to remove data e.g. truncate, delete and drop. The last one is a bit different because instead of removing data it just deletes the table. What is the difference between truncate and delete command in SQL or when to use truncate vs delete is one of the most frequently asked SQL interview questions?

What is table scan, index scan, and index seek in SQL? Interview Question

 Hello guys, a good understanding of how the index works and how to use them to improve your SQL query performance is very important while working in a database and SQL and that's why you will find many questions based upon indexes on Programming Job interviews. One of such frequently asked SQL questions is the real difference between table scan, index scan, and index seek? which one is faster and why? How does the database chooses which scan or seek to use? and How you can optimize the performance of your SQL SELECT queries by using this knowledge. In general, there are only two ways in which your query engine retrieves the data, using a table scan or by using an index.

When to use EXISTS and NOT EXISTS in SQL? SQL Server Example

 Hello guys, if you are wondering how to use the IF EXISTS and NOT EXISTS in SQL then you are at the right place. Earlier, I have shared how to use GROUP BY, WHERE, and HAVING clause and in this tutorial I will share how to use exists and not exists clause in SQL. The IF EXISTS and NOT EXISTS commands in T-SQL are covered in depth in this article. When comparing data sets using subqueries, it also illustrates why EXISTS should be preferred over IN and NOT EXISTS over NOT IN. 

Difference between Primary key vs Unique key in a Table

The primary key and unique key are two important concepts in a relational database and are used to uniquely identify a row in a table. Both primary key and unique keys can identify a row uniquely but there is some subtle difference between them which we will see in this article. In fact, primary key vs unique is a popular SQL interview question along with classics like truncate vs delete and  How to manage transactions in a database, mostly asked to fresher and 2 to 3 years experience guys in any programming language.