SQL LEFT JOIN Keyword

Submitted by admin on Wednesday, March 23, 2011 - 10:02.

There are times when you want to retrieve all data from one table even there is no matching record from both tables. Unlike SQL INNER Join, SQL LEFT Join will still show you the result from the left table or the first table. SQL INNER Join as discussed in the previous chapter will only show records that match on both tables based on the column you specified.

SQL LEFT JOIN Syntax

SELECT column_name(s) FROM First_table_name LEFT JOIN Second_table_name ON First_table_name.column_name = Second_table_name.column_name

Consider the following table for this exercise

Users

Firstname	Lastname	Salary	DeptID
John	Smith	1000	1
Mathew	Simon	3000	1
Bill	Steve	2200	1
Amanda	Rogers	1800	2
Steve	Hills	2800	2
Steve	jobs	2400	2
bill	cosby	700	3

Departments

DeptID	DepartmentName
1	Employee
3	Staff

Please take note that there is no DeptID 2 in our Departments table, but there is DeptID 2 in the Users table.

Example # 1

SELECT Firstname, Lastname, Salary, DepartmentName FROM Users LEFT JOIN Departments ON Users.DeptID = Departments.DeptID

Result of the Query

Firstname	Lastname	Salary	DepartmentName
John	Smith	1000	Employee
Mathew	Simon	3000	Employee
Bill	Steve	2200	Employee
Amanda	Rogers	1800
Steve	Hills	2800
Steve	jobs	2400
bill	cosby	700	Staff

We have joined the two tables and match both tables and include all rows from Users table.