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