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.
Tóm Tắt
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.