Left Join in SQL | The Most Detailed Explanation and Guide Ever

Hello colleagues! Today I decided to write probably the most selective tutorial on the web on the topic of LEFT JOIN in SQL. The point is that I chose this topic for a reason. Joining tables always raises the most questions for beginners about the logic and mechanism of application, and this section of the query language is very important. In my experience, 95% of the queries that I made over the long 10 years in IT included exactly the JOIN construct.

We will analyze LEFT JOIN not because it is most often asked about in interviews or it is most often found in test tasks, but using this construction as an example, we will understand the logic of applying and building queries with the JOIN keyword. I have divided the explanation into several sections in order to make it clear to everyone, finding an approach to each.

Example Explanation

So, imagine our two sets or, in simple terms, tables. The first table [employees] contains first names, last names, and a unique user ID in the database.

The second table [departments] contains information about which employee works in which department and also a unique identifier (by the way, similar data separations are made from the information security logic and more reasonable resource management of servers and databases)

Now let's try to join these two tables using LEFT JOIN. We have a common data column [emp_id], and the merging will take place just on it. For now, let's select all the columns for clarity. Let's build a query like this:

  SELECT *
    FROM employees
    LEFT JOIN departments ON employees.emp_id = departments.emp_id;            
          

What will happen? Technically, we've taken each row from the second table (the one after the JOIN statement) and mapped it to a common column. This is how the tables were joined.

Of course, we don’t need two identical columns, so we boldly limit the columns we need after the SELECT statement.

And finally get the result:

Euler Diagram

If you've never heard of the Euler diagram before, don't panic, I'll briefly. In a left join, we have two sets, A and B. Set A represents the left table, and set B represents the right table. The common column that we use to join the two tables is represented by the overlapping area between the two sets. I made a picture below by myself, so don't be picky.

SQL - Left Join

Let's briefly revisit our previous examples and two tables [employees, departments]. To perform a left join between the [employees] and [departments] tables, we start with the left table, which is the [employees] table. We then draw a circle to represent the set A, which contains all the employees in the table. We then draw a second circle to represent the set B, which contains all the departments in the [departments] table. We then draw an overlapping area between the two circles to represent the common column, which is the [emp_id] column.

SQL - Left Join Detailed Version

In this example, we want to join the two tables on the [departments] column. We include all the rows from the left table, which is the [employees] table, and match them with the matching rows from the right table, which is the [departments] table.

When we perform the left join, we keep all the rows from set A, which is the [employees] table, and match them with the matching rows from set B, which is the [departments] table. If there is no match, we include a row with NULL values for the right table columns.

Example

Suppose you are organizing a party and you have two lists: one contains the names of all the people you invited, and the other contains the names of the people who RSVP'd. You want to know who is coming to the party and who isn't.

To do this, you can perform a left join between the two lists, keeping all the names from the invitation list, even if they didn't RSVP. The result will include all the names from the invitation list, with the corresponding RSVP status (if any), or NULL values if the person didn't RSVP.

So this example can be illustrated using Euler diagrams. (I hope you don't go crazy with my schemes)

SQL - Left Join Example

As you can see, the left join keeps all the names from the invitation list, even if they didn't RSVP. The result includes the name of each person, along with their RSVP status (if any), or NULL values if they didn't RSVP. This can help you keep track of who is coming to the party and who isn't.

One More Example

Let me give you another example to make it clearer. Suppose you are a teacher and you have two lists: one contains the names of all the students in your class, and the other contains the grades for a recent exam. You want to know the grade for each student, even if they didn't take the exam.

To do this, you can perform a left join between the two lists, keeping all the names from the student list, even if they didn't take the exam. The result will include all the names from the student list, with the corresponding grade (if any), or NULL values if the student didn't take the exam.

So, we have students table

And we have garades table:

Combining the tables using the above algorithm, we get the following table, where it is logical to conclude that students with student_id 5 and 6, namely Mike and Hary, were not present at the exam, since they have NULL values in the grade column.

Multiple Joins

Of course, you can join not only two tables, two sets of data, but also several (as much as you want). It is important to remember that the logic and construction of the query, the syntax, is absolutely the same. Let's look at examples of sets. I think it will be clearer.

The most classic bunch of sets (tables) is the relationship of one table to several others. Such a bunch is called - One to many

SQL - Left Join Multiple Joins - One to Many

Everything is simple here - the set [employees] has one common column with a table [departments] and a [salaries] table. So they are connected and a query is built on this, which will look like this:

  SELECT *
    FROM employees
    JOIN departments ON departments.department_id = employees.department_id
    JOIN salaries ON salaries.department_id = employees.department_id;
                      
          

There are other types of linkages, such as many-to-one or many-to-many relations. Their names are completely optional to remember, just do not be surprised when you see similar names in any literature.

SQL - Left Join Multiple Joins - Many to One

At their core, they differ only in the number of so-called connectives, in other words, in the number of common columns with which connections could be made. So the query for a many-to-one relationship would look like this:

  SELECT *
    FROM employees
    JOIN salaries ON salaries.emp_id = employees.emp_id
    JOIN departments ON department_id.emp_id = employee.emp_id;
                      
          

SQL - Left Join Multiple Joins - Many to Many

Following exactly the same logic, a query for sets with a many-to-many relationship would look like this:

  SELECT *
    FROM employees
    JOIN departments ON departments.department_id = employees.department_id
    JOIN salaries ON salaries.employee_id = employees.employee_id
    JOIN managers ON managers.employee_id = employees.employee_id;     
          

By the way, in my 10 years of experience in IT, I rarely used such a bunch. The main thing I want to convey to you is that don't get scared when you need to join more than two tables. Absolutely the same logic works here, just more lines of code. The main thing is to find common columns and work with them.

Short Conclusion

I hope I managed to convey the main logic to you. My goal was not to teach you syntax, you can find it anywhere, in courses or free tutorials on the Internet. I just tried to build in your head the logic of building such queries. Hope I got it. Good luck with your learning!