LEFT JOIN

 

A LEFT JOIN (also called LEFT OUTER JOIN) returns:

• All rows from the left table
  
• Matching rows from the right table
  
• If no match is found, NULLs are returned for right table columns

SELECT A.column1, B.column2 FROM TableA A

LEFT JOIN TableB B

ON A.common_column = B.common_column;

=============================EXAMPLE==================================================

Employee table

emp_id	emp_name	dept_id
1	John	10
2	Alice	20
3	Bob	30

Department Table

dept_id	dept_name
10	HR
20	Finance

SELECT e.emp_name, d.dept_name FROM Employees e
LEFT JOIN Departments d
ON e.dept_id = d.dept_id;

OUTPUT:

emp_name	dept_name
John	HR
Alice	Finance
Bob	NULL

==============================EXAMPLE=================================================

Example

Using the same customers table as the previous example:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

And the orders table with the following data:

order_id	customer_id	order_date
1	7000	2016/04/18
2	5000	2016/04/18
3	8000	2016/04/19
4	4000	2016/04/20
5	NULL	2016/05/01

SELECT customers.customer_id, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

 

There will be 6 records selected. These are the results that you should see:

customer_id	order_id	order_date
4000	4	2016/04/20
5000	2	2016/04/18
6000	NULL	NULL
7000	1	2016/04/18
8000	3	2016/04/19
9000	NULL	NULL

This LEFT OUTER JOIN example would return all rows from the customers table and only those rows from the orders table where the joined fields are equal.

If a customer_id value in the customers table does not exist in the orders table, all fields in the orders table will display as NULL in the result set. As you can see, the rows where customer_id is 6000 and 9000 would be included with a LEFT OUTER JOIN but the order_id and order_date fields display NULL.