SQL OUTER JOIN overview and examples

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

SQL JOIN SQL JOIN

We can many SQL Join types in SQL Server. In the following image, you can see SQL Joins categories

SQL Join types SQL Join types

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.

  1. SQL Full Outer Join
  2. SQL Left Outer Join
  3. 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.

Table modelsTable models

Insert data into the Employee table with the following script.

Sample dataSample data

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

Sample dataSample data

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 JoinSQL Full Outer Join

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

SQL Full Outer Join exampleSQL Full Outer Join example

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.

SQL Full Outer Join exampleSQL Full Outer Join example

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 SQL 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 Full Outer Join exampleSQL Full Outer Join example

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.

SQL LEFT OUTER JOINSQL LEFT OUTER JOIN

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.

SQL LEFT OUTER JOINSQL LEFT OUTER JOIN

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 LEFT OUTER JOINSQL 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).

SQL Right OUTER JOINSQL Right OUTER JOIN

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.

SQL Right OUTER JOINSQL Right OUTER JOIN

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.

Right Outer JoinRight 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.

SQL JOINSQL 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.