Left Join Vs Left Outer Join In Sql

Left Join Vs Left Outer Join In Sql

In Sql, joins are used to combine data from two or more tables based on related columns between them. There are different types of joins in Sql like inner join, outer join, left join, right join etc. Among all these joins, Left Join and Left Outer Join are the most commonly used types of joins in Sql. In this article, we will discuss Left Join Vs Left Outer Join In Sql in detail.

Left Join

A Left Join in Sql returns all the rows from the left table and matched rows from the right table. If there is no match found in the right table for the rows in the left table, then it returns Null for the columns in the right table.

Syntax of Left Join:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

Example:

Let’s consider two tables “Employees” and “Department” and see how the Left Join works in Sql:

Employees Table:

EmployeeID EmployeeName DepartmentID
1 John 1
2 Smith 2
3 David 1
4 Sarah 3
5 Emily 2

Department Table:

DepartmentID DepartmentName
1 IT
2 Marketing
3 HR

In the above tables, the Employees table has 5 rows and the Department table has 3 rows. The Employee table has a DepartmentID column which is related to the Department table’s DepartmentID column.

Now, let’s write a query to fetch all the employees and their department names using the Left Join.

Query:

SELECT Employees.EmployeeName, Department.DepartmentName
FROM Employees
LEFT JOIN Department
ON Employees.DepartmentID = Department.DepartmentID;

Output:

EmployeeName DepartmentName
John IT
Smith Marketing
David IT
Sarah HR
Emily Marketing

From the above output, we can see that the Left Join in Sql returns all the employee names and their corresponding department names. In case, if there is no match found for any employee in the Department table, then it returns Null for that employee’s department name.

Left Outer Join

A Left Outer Join is similar to the Left Join, but it returns all the rows from the left table and matched rows from the right table, as well as all the unmatched rows from the left table. In case, if there is no match found in the right table for the rows in the left table, then it returns Null for the columns in the right table.

Syntax of Left Outer Join:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;

Example:

Let’s consider the same two tables “Employees” and “Department” and see how the Left Outer Join works in Sql:

Employees Table:

EmployeeID EmployeeName DepartmentID
1 John 1
2 Smith 2
3 David 1
4 Sarah 3
5 Emily 2

Department Table:

DepartmentID DepartmentName
1 IT
2 Marketing

In the above tables, the Employees table has 5 rows and the Department table has 2 rows. The Employee table has a DepartmentID column which is related to the Department table’s DepartmentID column.

Now, let’s write a query to fetch all the employees and their department names using the Left Outer Join.

Query:

SELECT Employees.EmployeeName, Department.DepartmentName
FROM Employees
LEFT OUTER JOIN Department
ON Employees.DepartmentID = Department.DepartmentID;

Output:

EmployeeName DepartmentName
John IT
Smith Marketing
David IT
Sarah Null
Emily Marketing

From the above output, we can see that the Left Outer Join in Sql returns all the employee names and their corresponding department names. In addition, it also returns the unmatched rows from the Employees table. In this case, Sarah’s DepartmentID is 3, which is not available in the Department table. Therefore, it returns Null for her department name.

Keywords:

Sql, Joins, Left join, left outer join, inner join, outer join, combine data, related columns, syntax, query, example, tables, Employees, Department, department name, employee name, employeeid, departmentid, marketing, IT, HR, unmatched rows.

Conclusion:

In conclusion, Left Join and Left Outer Join are the commonly used types of joins in Sql. Both joins are used to combine data from two or more related tables. The main difference between Left Join and Left Outer Join is that Left Outer Join returns all the unmatched rows from the left table, whereas Left Join only returns the matched rows. By using these joins, developers can extract useful insights from their data sets with ease.