Rajendra Gupta
SQL Join types

SQL OUTER JOIN overview and examples

April 16, 2019 by

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

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

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.

You can refer the following data model of both the tables.

Table models

Insert data into the Employee table with the following script.

Sample data

Insert Data into the Departments table

Sample data

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.

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

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

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 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

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 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 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 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

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 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).

You can notice the difference in Right Outer Join after swapping tables positions in above query.

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

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

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

2,806 Views