SQL LEFT JOIN statement

In this tutorial, you will learn how to retrieve data from two tables using SQL left join.

Using left join

A LEFT JOIN statement returns all rows from the left table and the rows from the right table that meet the join conditions. A left join isone type of outer join, and is therefore also known asleft outer join. Other variants of outer join areRight joinAndFull join.

The following Venn diagram illustrates how a left join works.

To clearly understand this, let's look at the followingemployeesAnddepartmentsTable.

+--------+--------------+------------+---------+
|　emp_id　|　emp_name　|　hire_date　|　dept_id　|
+--------+--------------+------------+---------+
|　　　　　　1　|　Ethan　Hunt　|　2001-05-01　|　　　　　　　4　|
|　　　　　　2　| Tony Montana |　2002-07-15　|　　　　　　　1　|
|　　　　　　3　| Sarah Connor |　2005-10-18　|　　　　　　　5　|
|　　　　　　4　| Rick Deckard |　2007-01-03　|　　　　　　　3　|
|　　　　　　5　| Martin Blank |　2008-06-24　|　NULL　|
+--------+--------------+------------+---------+

+---------+------------------+
|　dept_id　|　dept_name　|
+---------+------------------+
|　　　　　　　1　|　Administration　|
|　　　　　　　2　|　Customer　Service　|
|　　　　　　　3　|　Finance　|
|　　　　　　　4　|　Human　Resources　|
|　　　　　　　5　|　Sales　|
+---------+------------------+

Now, suppose you want to retrieve the ID, name, and hire date of all employees, as well as their department name, regardless of whether they are assigned to any department. To obtain this type of result set, we need to apply a left join.

The following statement retrieves data by using common fields toemployees(employees) anddepartmentsTables (employees) and (departments) are connected together to retrieve the employee ID, name, hire date, and department namedept_id. It also includes employees who have not been assigned to any department.

SELECT　t1.emp_id,　t1.emp_name,　t1.hire_date,　t2.dept_name
FROM　employees　AS　t1　LEFT　JOIN　departments　AS　t2
ON　t1.dept_id　=　t2.dept_id　ORDER　BY　emp_id;

After executing the above command, you will get the following output:

+--------+--------------+------------+-----------------+
|　emp_id　|　emp_name　|　hire_date　|　dept_name　|
+--------+--------------+------------+-----------------+
|　　　　　　1　|　Ethan　Hunt　|　2001-05-01　| Human Resources |
|　　　　　　2　| Tony Montana |　2002-07-15　| Administration |
|　　　　　　3　| Sarah Connor |　2005-10-18　| Sales |
|　　　　　　4　| Rick Deckard |　2007-01-03　| Finance |
|　　　　　　5　| Martin Blank |　2008-06-24　| NULL |
+--------+--------------+------------+-----------------+

As you can clearly see, the left join includesemployeesall rows in the result set of the table (employees), regardless ofdepartmentsin the tabledept_idColumn matching.