Table of Contents
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)
Emp_id | Emp_name | street | city | Emp_contact | Salary | Dept_id |
---|---|---|---|---|---|---|
101 | jone | althan | Surat | 1111111 | 20000 | 10001 |
102 | cartin | udhna | Surat | 2222222 | 15000 | 20001 |
103 | krish | ajava | Vadodara | 3333333 | 30000 | 20001 |
104 | dhiru | ramnagar | Vadodara | 8888888 | 36000 | 30001 |
105 | om | althan | Surat | 7777777 | 22000 | 30001 |
106 | adi | vesu | Navsari | 2323232 | 35000 | 10001 |
107 | annant | shivnagar | Navsari | 5555555 | 34000 | 10002 |
108 | yogi | althan | Surat | 8989898 | 25000 | 10002 |
109 | muskan | vesu | Vadodara | 9999999 | 18000 | 10001 |
110 | rudra | kashi | hazira | 1212121 | 31000 | 20001 |
Department Table (tbldept)
Dept_id | Dept_name | Dept_location |
---|---|---|
10001 | Account | Surat |
20001 | Sales | Hazira |
30001 | Finance | Vadodara |
10002 | Marketing | Surat |
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.
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');
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 FROM
and 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');
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 FROM
and 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;
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;
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;
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;
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;
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);
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
joints or joins?
What are JOINS in SQL?
“The joints 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. “
SQL joins are used to extract data which are present in multiple tables of relation database, basically >joints< in SQL are the commands which are used to combine rows from two or more tables based on related column between those tables.
Fixed the typo!