SQL - LEFT OUTER JOIN
The SQL LEFT OUTER JOIN keyword same as SQL LEFT JOIN keyword and it is used to combine column values of two tables based on the match between the columns. It returns all rows of the table on the left side of the join and matching rows of the table on the right side of the join. The rows of the left side table where there is no match in the right side table, the result table will contain NULL value.
Syntax
The syntax for using LEFT JOIN keyword is given below:
SELECT table1.column1, table1.column2, table2.column1, table2.column2, ... FROM table1 LEFT OUTER JOIN table2 ON table1.matching_column = table2.matching_column;
Example:
Consider a database tables called Employee and Contact_Info with the following records:
Table 1: Employee table
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
Table 2: Contact_Info table
Phone_Number | EmpID | Address |
---|---|---|
+1-80XXXXX000 | 2 | XXX, Brooklyn, New York, USA |
+33-14XXXXX01 | 3 | XXX, Grenelle, Paris, France |
+31-20XXXXX19 | 4 | XXX, Geuzenveld, Amsterdam, Netherlands |
+86-10XXXXX458 | 6 | XXX, Yizhuangzhen, Beijing, China |
+65-67XXXXX4 | 7 | XXX, Yishun, Singapore |
+81-35XXXXX72 | 8 | XXX, Koto City, Tokyo, Japan |
-
To left outer join Employee and Contact_Info tables based on matching column EmpID, the SQL code is given below. This will fetch Name and Age columns from Employee table and Address column from Contact_Info table.
SELECT Employee.Name, Employee.Age, Contact_Info.Address FROM Employee LEFT OUTER JOIN Contact_Info ON Employee.EmpID = Contact_Info.EmpID;
This will produce the result as shown below:
Name Age Address John 25 NULL Marry 24 XXX, Brooklyn, New York, USA Jo 27 XXX, Grenelle, Paris, France Kim 30 XXX, Geuzenveld, Amsterdam, Netherlands Ramesh 28 NULL Huang 28 XXX, Yizhuangzhen, Beijing, China -
To fetch all fields of a table, table.* keyword is used, for example - to fetch all fields of the Employee table, Employee.* is used in the below SQL code:
SELECT Employee.*, Contact_Info.Address FROM Employee LEFT OUTER JOIN Contact_Info ON Employee.EmpID = Contact_Info.EmpID;
This result of the following code will be:
EmpID Name City Age Salary Address 1 John London 25 3000 NULL 2 Marry New York 24 2750 XXX, Brooklyn, New York, USA 3 Jo Paris 27 2800 XXX, Grenelle, Paris, France 4 Kim Amsterdam 30 3100 XXX, Geuzenveld, Amsterdam, Netherlands 5 Ramesh New Delhi 28 3000 NULL 6 Huang China 28 2800 XXX, Yizhuangzhen, Beijing, China