- 4 years ago
- Zaid Bin Khalid
- 2062 Views
-
3
In this session, we will learn how to retrieve data from two tables by using SQL left join.
Using Left Joins
A Left Join statement is used to return all rows from the left table. And with the rows from the right table where the join condition met. The type of outer join is left join, it is also called the left outer join. Other types of outer join are called right join and full join.
In other words, we can say that The SQL left join returns all the values from the left table. And it also includes matching values from the right table, if there are no matching join value it returns NULL.
The mention below following the Venn diagram shows how left join works.
To understand this easily, let’s have a look at the following employees and departments tables
+--------+--------------+------------+---------+
| 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 |
+--------+--------------+------------+---------+
Table: employees
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Administration |
| 2 | Customer Service |
| 3 | Finance |
| 4 | Human Resources |
| 5 | Sales |
+---------+------------------+
Table: departments
The following given below example will show employee’s id, name, hiring date and department name by joining the employees and department’s tables together using the common dept_id field. It also shows those employees who are not assigned to a 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;
Tip: In a join query, the left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.
After executing the above command, you’ll get the output something like this.
+--------+--------------+------------+-----------------+
| 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 |
+--------+--------------+------------+-----------------+
It will clearly show you that the left join includes all the rows from the employee’s table in the result set. Whether or not there is a match on the dept_id column in the department table.
Note: If there is a row in the left table but no match in the right table, then the associated result row contains NULL values for all columns coming from the right table.
- 4 years ago
- Zaid Bin Khalid
- 2062 Views
-
3