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

Table 2: Contact_Info table

• 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