Module 3: Understanding Outer Join

Previous Tutorial - Next Tutorial

With Outer joins, you can request to return all rows from one or both sides of the join tables, even if there are no matching rows on the other side of the table, based on the ON keyword predicate. The difference between this outer and inner join is that in the case of Outer join, the un-match rows in the first table are still returned by the query while Inner join returns only matched rows.

This Outer join is divided into three parts. The first is the left outer join, the second one is the right outer join and the third one is the full outer join. First, let's start with the left outer join. A left outer join returns all the rows from the left table in conjunction with the matching rows from the right table. If no columns are matching in the right table, it returns the null values. This is the general syntax of the left outer join. The syntax is almost the same as Inner join, except the keyword left Outer join here.

In the diagram here in the left table, this B, C match up with the corresponding rows in the right-hand side table where the same values are found. Here notice that unlike the inner join, the left rows with the key A are returned, even though it has no match in the right side table and as there is no matching value in the right table, so it will return with the null values. Here the outer keyword is optional. If you ignore the outer keyword in the query, then also the query will return the result without any error.

Let's understand the right outer join. This join is opposite of the left outer join as its name suggests the right outer join returns all the rows from the right table in conjunction with the matching rows from the left table. No columns are matching in the left table, it returns null values. This is the general syntax of the right outer join. As you may have guessed, there is not too much difference in the syntax between a left outer join and a right outer join. Instead of left, we need to use the right outer join clause in the syntax.

In the diagram, in the right table, this B1, B2, C1 match up with the corresponding rows in the left side table where the same values are found. Now notice the right table row with the key B1 is returned even though it has no match in the left side table. There is no matching value in the left table, it will return with the null values. Once again, an outer keyword is optional here. If you have ignored the outer keyword in the query, then this query will return the result without any error.

Let's understand the full outer join. Full outer join combine's left and right outer join. The result returns from this type of join, including all rows from both the tables. When the conditions are met, then related values return and return a null value when there is no match. In other words, a full outer join is based on the fact that only the matching entries in the right or left of the tables or both of the tables should be listed. This is the general syntax of the full outer join where we use the full outer join clause to join the table.

In the diagram here in the right table, this B1, B2 and C1 match up with the corresponding rows in the left-hand side table. But the left table row with the key A1 and the right table row with the key D1 is still returned, even though it has no match on either side of the table and there is no matching value in either table, so it's a return with the null values. Let's understand each of these joins practically in SQL Server Management Studio.

First, start with the left outer join to understand each outer join we will use the same tables which we have used in the previous slide section. We will be going to join these two table customers and the person with the left outer join. We know that the left outer join will return all the rows from the left table and matching rows from the right table. If there are no matches in the right table, then it will return the null values for those columns. Here, the customer table if I execute this query, then the customer table will contain 19820 records. To join this person table, I have to use the left outer join(LEFT OUTER JOIN Person.Person ON customer.PersonID = person.BusinessEntityID).

Instead of a star, let me write this specific column. A star will return all the columns. Now if I execute this query, then it will give me all the records from this left table, which is the customer table, and matching rows from this person table. Those records that do not match with this person's table then will return the null values.

You can also use the graphic query editor feature of SQL Server Management Studio to build the same query. For that right-click on this query editor select design query in editor, which will open this window. Now here, let us select the same table, this customer, and then select the person table. Remove this existing join by right click on it and click remove.

We want to build the left outer join on this person ID column with this business entity ID column. Select this person ID column, join with this business entity column and then right-click on this and select the first option which is to select all rows from the customer and now select the appropriate column, click on OK.

This query will give me the same result as this query, Now let's move on to the right outer join. As we learn that the right outer join will return all rows from the right table, in this case, it is the person table and the matching rows from the left table. If there are no matches in the left table, then it will return the null values for those columns. Instead of left, I just need to write the query.

If I execute this query, then it will give me all rows from this person's table and the matching rows from the customer table. With the right outer join now we will get the 19972 records. Now to confirm the same, if I execute this query, then it will give me the same count. A right outer join will return all the rows from the right table and matching rows from the left table.

Let's understand the full outer join. This join includes all the rows from both tables. When the conditions are met, the related values return. If there are no matches in either table, then it will return the null values for those columns. In order to use the full outer join, I just need to write the query. Now, if I execute this query, it will give me the 20673 rows, which means it includes all the rows from both the table customer and person.

Wherever the conditions are matched, it will return the related rows from the table and when the conditions are not matched, then it will return with the null values. Here the outer keyword is optional. If I remove this outer keyword and execute the query again, then it will give me the same result. Aside from the outer join part in the next section, we will understand the cross join.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials:

• General SQL Introduction
• Module 1: Getting Started with SQL Server
• Module 1: SQL Server Management
• Module 1: Essential SQL Commands
• Module 1: SQL Introduction Essentials
• Module 2: Learning SQL Overview
• Module 2: Logical Query Processing Order
• Module 2: Select Statement Fundamentals
• Module 2: Filtering Data with WHERE Clause Overview
• Module 2: Filter Data with WHERE Clause - Part 1
• Module 2: How to Filter Data with WHERE Clause - Part 2
• Module 2: Filter PDF Data Using WHERE clause - Part 3
• Module 2: Sorting Data Using ORDER BY Clause
• Module 2: Grouping Data with GROUP BY Clause Part 1
• Module 2: Grouping Data with GROUP BY Clause Part 2
• Module 2: Grouping Data with GROUP BY Clause Part 3
• Module 2: Limiting Data With TOP Clause
• Module 2: Summary and Quiz
• Module 3: SQL Joins and SET Operators
• Module 3: Understanding Inner Join
• Module 3: Understanding Outer Join
• Module 3: Cross Join Explained
• Module 3: Self Join Explained
• Module 3: UNION and UNION ALL
• Module 3: INTERSECT and EXCEPT
• Module 3: Understanding Sub-Queries
• SQL Analytic Functions