Tutorial Gateway

SQL Outer Joins

by

In this article, we will show you how to write a SQL Query to Join two or more tables using the SQL Outer Joins with an example. It is one of the Frequently Asked Question.

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

  1. Right Outer Join: Also called as Right Join. It returns matching rows from the left table (if any), and all the rows present in the Right table.
  2. Left Outer join: Or called as Left Join. It returns all the rows present in the Left table and matching rows from the right table (if any).
  3. Full Outer Join: Also called as Full Join. It returns all the rows present in both the Left table and the right table.

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

SQL Outer Joins Example 1

Data present in the Department Table is:

SQL Outer Joins Example 2

SQL Outer Join Examples

Following is the list of SQL Outer Joins. Below are examples that demonstrate each outer join.

SQL Right Outer Join Example

In this example, we will show you how to write Right Outer Join. For this, We are going to use the tables that we have shown above. We had already explained the Right Join in our previous article. So, I suggest you refer the same for better understanding

--SQL Outer Join :- Example for RIGHT OUTER JOIN 
USE [SQLTEST]
GO
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  RIGHT JOIN [Department] AS Dept
  --RIGHT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID

OUTPUT

SQL OUTER JOINS Example 1

SQL Left Outer Join Example

This Outer Join example shows you how to write Left Outer Join. We had already explained the Left Join in our previous article. So, I suggest you refer the same for better understanding

--SQL OUTER JOIN :- Example for LEFT OUTER JOIN 
USE [SQLTEST]
GO
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

OUTPUT

SQL OUTER JOINS Example 2

Full Outer Join Example

In this outer join example, we will show how to write a Full Outer Join. We had already explained the Full Join in our previous article. So, I suggest you refer them for better understanding

--SQL Server OUTER JOIN :- Example for FULL OUTER JOIN 
USE [SQLTEST]
GO
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

OUTPUT

SQL OUTER JOINS Example 3