SQL LEFT JOIN:

The LEFT JOIN returns all the rows from the first table , even if there are no matches in the second table. If there are rows in first table that do not have matches in second table, so that rows also will be listed.

 Syntax:

SELECT column_name(s)

FROM first_table

LEFT JOIN second_table

ON first_table. column_name= second_table.column_name

 Note:

In some databases LEFT JOIN is called LEFT OUTER JOIN. So the syntax is

Syntax:

SELECT column_name(s)

FROM first_table

LEFT OUTER JOIN second_table

ON first_table. column_name= second_table.column_name

In the “Employees” table below, the “Employee_ID” column is the primary key, meaning that no two rows can have the same Employee_ID. The Employee_ID distinguishes two persons even if they have the same name.

When you look at the example tables below, notice that:

  • The “Employee_ID” column is the primary key of the “Employees” table
  • The “Prod_ID” column is the primary key of the “Orders” table
  • The “Employee_ID” column in the “Orders” table is used to refer to the persons in the “Employees” table without using their names

Employees:

Employee_ID Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

Orders:

Prod_ID Product Employee_ID
234 Printer 01
657 Table 03
865 Chair 03

Example:

List all employees, and their orders – if any.

Query:

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID=Orders.Employee_ID

Result:

Name Product
Hansen, Ola Printer
Svendson, Tove
Svendson, Stephen Table
Svendson, Stephen Chair
Pettersen, Kari

Leave a Reply

Your email address will not be published. Required fields are marked *