SQL LEFT OUTER JOIN Explained with Examples

In this instructive article on SQL Left Outer Join you will get delineates about Concept of Joins with types in SQL , detailed explanation on Left Join with Practical examples to perform SQL left Outer Join.

What are JOINS in SQL?

SQL joins are used to extract data which are present in multiple tables of relation database, basically joins in SQL are the commands which are used to combine rows from two or more tables based on related column between those tables.

The joins are predominantly used when a user is trying to fetch or get data from tables which have one to many or many to many relationships between them. Using joins a user can extract data from two or more tables based on a related column between them.

The keywords used to specify join conditions are WHERE, ON, USING, NATURAL and CROSS. WHERE can be used to create a join between tables without using the keyword JOIN, but it can only be used for inner joins. ON is used with JOIN in a join condition to identify which columns is each table to link and can be used with all types of joins. USING is also combined with JOIN in a join condition, but it requires that the column name be identical in both joined tables.

Types of SQL JOINS

SQL LEFT JOIN | SQL LEFT OUTER JOIN

The left join or the left outer join statement returns all the records from left table and also those records which satisfy a condition from the right table, also for the records having no matching values in right table, the output or resulting dataset will contain NULL values.

SQL LEFT OUTER JOIN Syntax

Here, Table1 would be consider as a left table and Table2 would be consider as a right table. Table1.column1, Table1.column2, Table2.column1, are the name of the columns which you want to retrieve separated by comma.

SELECT Table1.column1, Table1.column2, Table2.column1, Table2.column2... 
FROM Table1 
LEFT JOIN Table2 
ON 
Table1.matchingcolumnname=Table2.matchingcolumnname

Suppose we working with two tables , Table 1 is employee table with emp_id as a primary key and dept_id as a foreign key. Table 2 is department table with dept_id as a primary key.
Employee Table (tblemp)

Department Table (tbldept)

SQL LEFT OUTER JOIN | Examples

How can we use Left Outer join to list Employee details with the Department name

In this example, the data required is all the employee details along with their department name, this means all the employee details have to be listed even though their corresponding department information is not present. In above query we have used AS command to rename a column with an alias.

SELECT tblemp.Emp_name AS Name, tblemp.street AS Street, tblemp.city, tblemp.Emp_contact AS Contact, tblemp.Salary, tbldept.Dept_name AS Department 
FROM tblemp 
LEFT OUTER JOIN tbldept 
ON 
tblemp.Dept_id = tbldept.Dept_id; 

The LEFT JOIN can be used which returns all the rows from first table (i.e., tblemp), even if there are no matches in the second table (i.e.,tbldept). This means, if there are employee in tblemp that do not have any department in tbldept, those rows will also be listed.

OUTPUT:

List Employee details living in Surat city along with Department name by  left joining tblemp to tbldept 

In this example, we will display employee details for which having “Surat” value in city column along with department name from department table, as we have done in pervious example.

We LEFT JOIN employee table (i.e., tblemp) with department table (i.e., tbldept) with LEFT JOIN and we will get all records of employee table whose is having Surat value in city whether it is having Dept_id value or not.

SELECT tblemp.Emp_name AS Name, tblemp.street AS Street, tblemp.city AS City, tblemp.Emp_contact AS Contact, tblemp.Salary, tbldept.Dept_name AS Department 
FROM tblemp 
LEFT OUTER JOIN  tbldept 
ON 
tblemp.Dept_id = tbldept.Dept_id 
WHERE (tblemp.city = 'Surat'); 

OUTPUT:

List Employee details living in Vadodara City and working in Sales Department with SQL Left join statement.

In Following SQL left join query, LEFT OUTER JOIN clause that is between tblemp and tbldept tables derived all the matched records from left table tblemp which are having Vadodara as city value  and having dept_id of sales department. The ON condition indicates how the two tables (the one after FROMand the one after  LEFT OUTER JOIN) should be combined. In the example above that both tables contain the column dept_id.

SELECT tblemp.Emp_name AS Name, tblemp.street AS Street, tblemp.city AS City, tblemp.Emp_contact AS Contact, tblemp.Salary, tbldept.Dept_name AS Department 
FROM tblemp 
LEFT OUTER JOIN tbldept 
ON 
tblemp.Dept_id = tbldept.Dept_id 
WHERE 
(tblemp.city = 'Vadodara') 
AND 
(tbldept.Dept_name = 'Sales'); 

OUTPUT:

With SQL Left Outer Join display Department info with its Employee name in ascending order of Department name

In contrast to previous example in this query tbldept is left join to tblemp to derived all the matched records from left table tbldept with right table tblemp by first comparing dept_id of department table with employee table and display all employee name whose department id is present in department table. same as above query ON command is used between tables to indicates how the two tables (the one after FROMand the one after  LEFT OUTER JOIN) should be combined with comma column dept_id.

SELECT tbldept.Dept_id AS [Department ID], tbldept.Dept_name AS [Department Name], tbldept.Dept_location AS [Department Location], tblemp.Emp_name AS [Employee Name] 
FROM  tbldept 
LEFT OUTER JOIN  tblemp 
ON 
tbldept.Dept_id = tblemp.Dept_id 
ORDER 
BY [Department Name]; 

OUTPUT:

Display Department wise total number of Employees by left joining department table with employee table

In below query the first step, we should combine the tbldept and tblemp tables through the left outer join clause because left join returns all the matched rows of tbldept with tblemp tables. In the second step, we will combine the tbldept table to the tblemp table through the left join and then filter the null values because we need to eliminate the rows which are stored by the tblemp table:

SELECT tbldept.Dept_name AS Department, COUNT (*) AS Employee 
FROM  tbldept 
LEFT OUTER JOIN tblemp 
ON tbldept.Dept_id = tblemp.Dept_id 
GROUP BY tbldept.Dept_name ORDER BY Department; 

OUTPUT:

Display each Department’s highest salary using SQL Left Outer join

Consider following example, tbldept is left join with tblemp to extract rows with highest salary of each department. the GROUP BY clause used in this query to make group of department name and apply aggregate function MAX on it. ORDER BY clause is used to display result in ascending order of department name .

SELECT tbldept.Dept_name AS Department, MAX(tblemp.Salary) AS [Highest Salary] 
FROM  tbldept 
LEFT OUTER JOIN tblemp 
ON tbldept.Dept_id = tblemp.Dept_id GROUP BY tbldept.Dept_name 
ORDER BY Department;

OUTPUT:

Apply SQL Left join to display name of Employees getting highest salary from each Department 

The following query is example of left outer join with subquery where we first find list of highest salary of each department using MAX(tblemp.salary) and group by tbldept.Dept_name to make group of departments and then in outer query we put where clause condition to retrieve those rows in which salary value is equal to result of subquery. Also to display result in order of department name ORDER BY clause is used.

SELECT tbldept.Dept_name AS Department, tblemp.Emp_name, tblemp.Salary 
FROM tbldept 
LEFT OUTER JOIN  tblemp 
ON tbldept.Dept_id = tblemp.Dept_id 
WHERE (tblemp.Salary 
IN (SELECT MAX(tblemp_1.Salary) AS Expr1 FROM tblemp AS tblemp_1 
INNER JOIN tbldept AS tbldept_1 
ON tblemp_1.Dept_id = tbldept_1.Dept_id GROUP BY tbldept_1.Dept_name)) 
ORDER BY Department; 

OUTPUT:

Display name of Employees getting highest salary from each City by connecting department table with employee table using SQL Left join

In the following left outer join query tblemp is left table and join with tbldept to display employee highest salary city wise , to make group of city in below query we have used group by clause with left join and ON command is used to connect both tables with common column dept_id.

SELECT tblemp_1.city, MAX(tblemp_1.Salary) AS [Highest Salary] 
FROM tblemp AS tblemp_1 
INNER JOIN  tbldept AS tbldept_1 
ON tblemp_1.Dept_id = tbldept_1.Dept_id 
GROUP BY tblemp_1.city; 

OUTPUT:

To display total salary paid by each Department how can we use SQL Left join statement

Here in below query tblemp is left outer join with tbldept to find total salary paid by each department so for that first me used left join tbldept to connect with value of department and second we apply GROUP BY clause to make group of department than apply SUM aggregate function on salary which is column of salary.

SELECT tbldept_1.Dept_name AS Department, SUM(tblemp_1.Salary) AS [Total Salary] 
FROM tblemp AS tblemp_1 
INNER JOIN  tbldept AS tbldept_1 
ON tblemp_1.Dept_id = tbldept_1.Dept_id 
GROUP BY tbldept_1.Dept_name;

OUTPUT:

Find list of employees working in Account, living in Navsari city and having more than 20000 salaries with help of SQL Outer Update 

In succeeding query example, we first connect tblemp with tbldept using SQL left outer join and apply three condition with WHERE clause on city , department name and salary to extract those rows which has city as Navsari, department name as Account and having salary more than 20000. using ON command we connect common column dept_id of both tables.

SELECT tblemp.Emp_name AS Name, tblemp.street AS Street, tblemp.city AS City, tblemp.Emp_contact AS Contact, tblemp.Salary, tbldept.Dept_name AS Department 
FROM tblemp 
LEFT OUTER JOIN tbldept 
ON tblemp.Dept_id = tbldept.Dept_id 
WHERE (tblemp.city = 'Navsari') AND (tbldept.Dept_name = 'Account') AND (tblemp.Salary > 20000); 

OUTPUT:

Summary

In this Article, we have learning about joins in SQL specifically covered left outer joins with an examples on SQl Server Sql terminal. left outer join is one of the JOIN operations that allow you to preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.

Further Reading

https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj18922.html