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 joints in SQL are the commands which are used to combine rows from two or more tables based on related column between those tables.
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. 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.
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.
Table-1: Employee Table (tblemp)
Table-2: 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.
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
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
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
FROMand the one after
LEFT OUTER JOIN) should be combined with comma column
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];
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
tblemp tables through the left outer join clause because left join returns all the matched rows of
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
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
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
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);
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.