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.