This article will provide a full overview, with examples of the SQL Outer join, including the full, right and left outer join as well as cover the union between SQL left and right outer joins.
It is essential to understand the process to get the data from the multiple tables. A beginner might not have the idea of Joins in SQL Server. In this tip, we will take an overview of the SQL joins, learn SQL OUTER JOIN along with its syntax, examples, and use cases.
In a relational database system, it is best practice to follow the principles of Normalization, in which, basically, we split large tables into the smaller tables. In a select statement, we can retrieve the data from these tables using joins. We can join the tables and get the required fields from these tables in the result set. These tables should have some common field to relate with each other. You might find data split across multiple databases and sometimes it is a very complex structure as well. With Joins, we can join the data together from the multiple tables, databases into a user-friendly way and represent this data in the application.
We can represent a SQL JOIN using the following image
We can many SQL Join types in SQL Server. In the following image, you can see SQL Joins categories
Let’s explore SQL Outer Join in details in the upcoming section.
Overview of the SQL OUTER JOIN
We use the SQL OUTER JOIN to match rows between tables. We might want to get match rows along with unmatched rows as well from one or both of the tables. We have the following three types of SQL OUTER JOINS.
- SQL Full Outer Join
- SQL Left Outer Join
- SQL Right Outer Join
Let’s explore each of SQL Outer Join with examples.
SQL Full Outer Join
In SQL Full Outer Join, all rows from both the tables are included. If there are any unmatched rows, it shows NULL values for them.
We can understand efficiently using examples. Let’s create a sample table and insert data into it.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE [dbo].[Employee]( [EmpID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED, [EmpName] [varchar](50) NULL, [City] [varchar](30) NULL, [Designation] [varchar](30) NULL] ) CREATE TABLE Departments (EmpID INT PRIMARY KEY CLUSTERED , DepartmentID INT, DepartmentName VARCHAR(50) ); |
You can refer the following data model of both the tables.
Insert data into the Employee table with the following script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 |
USE [SQLShackDemo]; GO SET IDENTITY_INSERT [dbo].[Employee] ON; GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (1, N'Charlotte Robinson', N'Chicago', N'Consultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (2, N'Madison Phillips', N'Dallas', N'Senior Analyst' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (3, N'Emma Hernandez', N'Phoenix', N'Senior Analyst' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (4, N'Samantha Sanchez', N'San Diego', N'Principal Conultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (5, N'Sadie Ward', N'San Antonio', N'Consultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (6, N'Savannah Perez', N'New York', N'Principal Conultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (7, N'Victoria Gray', N'Los Angeles', N'Assistant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (8, N'Alyssa Lewis', N'Houston', N'Consultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (9, N'Anna Lee', N'San Jose', N'Principal Conultant' ); GO INSERT INTO [dbo].[Employee] ([EmpID], [EmpName], [City], [Designation] ) VALUES (10, N'Riley Hall', N'Philadelphia', N'Senior Analyst' ); GO SET IDENTITY_INSERT [dbo].[Employee] OFF; GO |
Insert Data into the Departments table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
USE [SQLShackDemo]; GO INSERT INTO [dbo].[Departments] ([EmpID], [Department_ID], [DepartmentName] ) VALUES (1, 0, N'Executive' ); GO INSERT INTO [dbo].[Departments] ([EmpID], [Department_ID], [DepartmentName] ) VALUES (2, 1, N'Document Control' ); GO INSERT INTO [dbo].[Departments] ([EmpID], [Department_ID], [DepartmentName] ) VALUES (3, 2, N'Finance' ); GO INSERT INTO [dbo].[Departments] ([EmpID], [Department_ID], [DepartmentName] ) VALUES (4, 3, N'Engineering' ); GO INSERT INTO [dbo].[Departments] ([EmpID], [Department_ID], [DepartmentName] ) VALUES (5, 4, N'Facilities and Maintenance' ); GO INSERT INTO [dbo].[Departments] ([EmpID], [Department_ID], [DepartmentName] ) VALUES (6, 2, N'Finance' ); GO INSERT INTO [dbo].[Departments] ([EmpID], [Department_ID], [DepartmentName] ) VALUES (10, 4, N'Facilities and Maintenance' ); GO |
We can represent a logical relationship between two tables using a Venn diagram. In a Venn diagram contains multiple overlapping circles and each circle represents an entity or table. The common area or overlapping area in Venn diagram represents the common values between both tables.
For example, in the following screenshot, we have two overlapping circles. Each circle resent a table (Employee and Departments). Let’s understand the FULL Outer Join using the following example.
We have a common field ( EmpID) in both the tables; therefore, we can join the table with this column. In the following query, we defined the FULL OUTER JOIN between departments and Employee table on the EMPID column of both the table.
|
1 2 3 |
SELECT * FROM Employee FULL OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID; |
SQL Full Outer Join gives following rows in an output
- Matching Rows between both the tables
- Unmatched Rows from both the tables (NULL values)
Let’s execute this query to return Full Outer Join query output. We get the following output.
We can see a few records with NULL values as well. Let’s understand this in a better way using a Venn diagram.
In the following screenshot, you can see the following information
- EmpID 1, 2,3,4,5,6,10 exists in both Employee and Departments table. In Full Outer Join query output, we get all these rows with data from both the tables
- EmpID 7, 8, 9 exists in the Employee table but not in the Departments table. It does not include any matching rows in the departments table; therefore; we get NULL values for those records
Now, for demo purpose let’s insert one more record in Departments tables. In this query, we insert EmpID 11 that does not exist in the Employee table.
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO [dbo].[Departments] ([EmpID], [Department_ID], [DepartmentName] ) VALUES (11, 4, N'Facilities and Maintenance' ); GO |
Rerun the SQL Full Outer Join query. In the following image, you get one additional row with NULL values. We do not have any matching row for EmpID 11 in the employee table. Due to this, we get NULL values for it in the output.
As a summary, we can represent the SQL Full Outer Join using the following Venn diagram. We get what is represented in the highlighted area in the output of Full Outer Join.
SQL FULL OUTER JOIN and WHERE clause
We can add a WHERE clause with a SQL FULL OUTER JOIN to get rows with no matching data between the both Join tables.
In the following query, we add a where clause to show only records having NULL values.
|
1 2 3 4 5 |
SELECT * FROM Employee FULL OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID WHERE Employee.EmpID IS NULL OR Departments.EmpID IS NULL; |
Execute this command and view the output. It only returns rows that do not match either in Employee or Departments table.
SQL LEFT OUTER JOIN
In a SQL Left Outer Join, we get following rows in our output.
- It gives the output of the matching row between both the tables
- If no records match from the left table, it also shows those records with NULL values
Execute the following code to return SQL LEFT OUTER JOIN output
|
1 2 3 |
SELECT * FROM Employee LEFT OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID |
In the following image, you can see we have NULL values for EmpID 7,8 and 9. These EmpID does not exist in the right side Department table.
We need to note the table placement position in the Join statement. Currently, we have an Employee table on the left side and Departments table in Right side.
Let’s rewrite query and swap the position of tables in query. In this query, we have the Department table in left position, so the Left Outer Join should check the values for this table and return a NULL value in case of a mismatch.
In the following screenshot, you can see that only one NULL value for EmpID 11. It is because EmpID 11 is not available in the Employee table.
As a summary, we can represent SQL Left Outer Join using the following Venn diagram. We get the highlighted area in the output of SQL Left Outer Join.
SQL Right OUTER JOIN
In SQL Right Outer Join, we get the following rows in our output.
- It gives the output of the matching row between both the tables
- If no records match from the right table, it also shows those records with NULL values
Execute the following query to get the output of Right Outer Join
|
1 2 3 |
SELECT * FROM Employee RIGHT OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID |
In the following image, you can see we get all matching rows along with one row with NULL values. Null value row has EmpID 11 because it does not exist in the Employee table. You can also notice the position of the Department table is in the right position in Join. Due to this, we do not get values from the Employee table (left position) which does not match with Department table (Right side).
As highlighted earlier, the table position is important in the JOIN statement. If we change the table positions, we get different output. In the following query, we have Departments table (Left) and Employee table (Right).
|
1 2 3 4 |
SELECT * FROM Departments RIGHT OUTER JOIN Employee ON Departments.EmpID = Employee.EmpID |
You can notice the difference in Right Outer Join after swapping tables positions in above query.
As a summary, we can represent the SQL Right Outer Join using the following Venn diagram. We get highlighted area in the output of SQL Right Outer Join.
The union between SQL Left Outer Join and SQL Right Outer Join
In the previous examples, we explored the SQL Left Outer Join, and the SQL Right Outer Join with different examples. We can do a Union of the result of both SQL Left Outer Join and SQL Right Outer Join. It gives the output of SQL Full Outer Join.
Execute the following query as an alternative to SQL Full Outer Join.
|
1 2 3 4 5 6 7 |
SELECT * FROM Employee LEFT OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID UNION ALL SELECT * FROM Employee RIGHT OUTER JOIN Departments ON Employee.EmpID = Departments.EmpID |
In the following output, we get all matching records, unmatch records from the left table and unmatch records from the right table. It is similar to an output of SQL Full Outer Join.
Conclusion
In this article, we explored the SQL Outer Join and its types along with examples. I hope you found this article helpful. Feel free to provide feedback in the comments below.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023