Understanding Left Joins in SQL

Introduction to Left Joins in SQL

A left join is a type of join operation used in SQL (Structured Query Language) and other database management systems to combine data from two or more tables based on a related column between them. In a left join, all rows from the left table are included in the result, and if there is no matching row in the right table, NULL values are returned for the right table’s columns.

The left join is commonly used when you want to retrieve data from one table and include related information from another table, even if there is no matching data in the second table. This allows you to preserve the records from the left table and still display the related information from the right table, if it exists.

Let’s consider two tables, table_a and table_b, as shown below:

table_a
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
|  3 | Carol |
+----+-------+

table_b
+----+----------+
| id | job      |
+----+----------+
|  1 | Engineer |
|  2 | Doctor   |
|  4 | Teacher  |
+----+----------+

In these tables, the id column is the related column.

Now let’s perform a left join between these two tables:

SELECT a.id, a.name, b.id, b.job
FROM table_a AS a
LEFT JOIN table_b AS b
ON a.id = b.id;

The result of the left join will be:

+----+-------+------+------+---------+
| a.id| a.name| b.id  | b.job       |
+----+------+------+---------+
|  1 | Alice |  1    | Engineer |
|  2 | Bob   |  2    | Doctor   |
|  3 | Carol | NULL | NULL     |
+----+------+------+---------+

As you can see, all records from table_a (the left table) are included in the result, and the matching records from table_b (the right table) are included where they exist. In this case, there is no matching row for the id value of 3 in table_b, so NULL values are returned for the columns from table_b.