SQL Outer Joins

In this article, we will show you how to write a query to Join two or more tables using the SQL Outer Joins with an example.

Types of Outer Joins in SQL Server?

There are three types of Outer Joins in SQL, and they are :

  • Right Outer is also called Right Join. It returns matching rows from the left table (if any) and all the rows present in the Right table.
  • Left Outer or called Left Join. It returns all the rows present in the Left table and matching rows from the right table (if any).
  • Full: It returns all the rows in both the left and right tables.

For this SQL Server example, We are going to use the below-shown data

Left Table Data 1

Data present in the Department Table is:

Right Table Records 2

SQL Outer Join Examples

The following is the list of available ones. The following examples demonstrate each one of them with a particle example.

In this example, we will show you how to write the Right Outer. For this. demo, we are going to use the tables that we have shown above. We had already explained the Right in our previous article.

SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  RIGHT JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
SQL Server OUTER JOINS Example 1

SQL Server Left Outer Join Example

This example shows you how to write the Left Outer Join on two tables that we have shown earlier. We had already explained the Left in our previous article.

SELECT Emp.[FirstName] AS [First Name]
      ,Emp.[LastName] AS [Last Name]
      ,Emp.[Education]
      ,Emp.[YearlyIncome] AS [Yearly Income]
      ,Emp.[Sales]
      ,Dept.[DepartmentName] AS [Department]
  FROM [Employees] AS Emp
	LEFT OUTER JOIN 
		[Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
SQL Left Outer Joins Example 2

Full Join Example

In this example, we will show how to write a Full Outer Join. We had already explained the Full in the previous article.

SELECT Emp.[FirstName] AS [First Name]
      ,Emp.[LastName] AS [Last Name]
      ,Emp.[Education]
      ,Emp.[YearlyIncome] AS [Yearly Income]
      ,Emp.[Sales]
      ,Dept.[DepartmentName] AS [Department]
  FROM [Employees] AS Emp
	FULL OUTER JOIN 
		[Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
SQL Full OUTER JOINS Example 3

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.