How to Outer Join Tables in SQL Server
This tutorial will show you how to use the outer joins on tables. Outer joins make use of the JOIN keyword between the table names to let SQL Server know to mark a table as “preserved”. There are three different types of outer joins: LEFT, RIGHT, and FULL. The LEFT keyword means that the rows of the left table are preserved, the RIGHT keyword means that the rows int he right table are preserved, and the FULL keyword means that the rows in both the left and right tables are preserved. We will show examples of each outer join, as this is the best way to understand them.
Setting Up
To work with joins, we need two tables. One table will be called Department with columns of DepartmentId and DepartmentName. The other table will be called Employees with columns of LastName and DepartmentId. The DepartmentId in the Department table will be the primary key while the DepartmentId in the Employee table will be the foreign key.
Employee Table Department Table
LEFT OUTER JOIN
We will first start with the LEFT join. This will preserve the rows of the left table, meaning all rows will appear no matter what. Like other joins we must first use the SELECT statement to declare which columns to select from the table, in this case all of them. Next we declare what table we want the columns selected from with the FROM statement. We next use the LEFT JOIN keyword to preserve the rows in the left table and then use the ON keyword to output the same instances of DepartmentId in both tables.
Execute the query and make sure it completes successfully. You should see a table with columns of LastName, two DepartmentIds, and DepartmentName.
As you can see, all the rows in the Employee table are displayed while we are missing the Marketing department on the right side. This is because we Left Joined the table, so all the rows on the left side were preserved.
RIGHT OUTER JOIN
In this next example we will RIGHT join the table and a Marketing row will appear. The syntax is exactly the same as the previous join, the only difference is we change LEFT to RIGHT.
Execute the query and make sure it completes successfully. The output will be the same as the LEFT join but with the exception of a new Marketing row.
A FULL join combines the LEFT and RIGHT joins to get all rows from both tables. So all the rows that appeared with the LEFT and RIGHT join queries will appear together regardless of null values.
We use the UNION operator to combine both SELECT statements and to not allow duplicates. Execute the query and make sure it completes successfully.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
OuterJoins.zip
Setting Up
To work with joins, we need two tables. One table will be called Department with columns of DepartmentId and DepartmentName. The other table will be called Employees with columns of LastName and DepartmentId. The DepartmentId in the Department table will be the primary key while the DepartmentId in the Employee table will be the foreign key.
Employee Table Department Table
We used over 10 web hosting companies before we found Server Intellect. They offer dedicated servers, and they now offer cloud hosting!
Enter the information shown above in to your tables.LEFT OUTER JOIN
We will first start with the LEFT join. This will preserve the rows of the left table, meaning all rows will appear no matter what. Like other joins we must first use the SELECT statement to declare which columns to select from the table, in this case all of them. Next we declare what table we want the columns selected from with the FROM statement. We next use the LEFT JOIN keyword to preserve the rows in the left table and then use the ON keyword to output the same instances of DepartmentId in both tables.
Code Block
LeftOuterJoin.sqlce
Left Outer Join Query.SELECT *
FROM Employee
LEFT JOIN Department
ON Employee.DepartmentId = Department.DepartmentId;
As you can see, all the rows in the Employee table are displayed while we are missing the Marketing department on the right side. This is because we Left Joined the table, so all the rows on the left side were preserved.
RIGHT OUTER JOIN
In this next example we will RIGHT join the table and a Marketing row will appear. The syntax is exactly the same as the previous join, the only difference is we change LEFT to RIGHT.
Code Block
RightOuterJoin.sqlce
Right Outer Join query.
SELECT *
FROM Employee
RIGHT JOIN Department
ON Employee.DepartmentId = Department.DepartmentId;
We stand behind Server Intellect and their support team. They offer dedicated servers, and they are now offering cloud hosting!
FULL OUTER JOINA FULL join combines the LEFT and RIGHT joins to get all rows from both tables. So all the rows that appeared with the LEFT and RIGHT join queries will appear together regardless of null values.
Code Block
FullJoin.sqlce
Full join query.SELECT *
FROM Employee
LEFT JOIN Department
ON Employee.DepartmentId = Department.DepartmentId
UNION
SELECT *
FROM Employee
RIGHT JOIN Department
ON Employee.DepartmentId = Department.DepartmentId;
We chose Server Intellect for its cloud hosting, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
The table is similar to the RIGHT join query output but in different order. It includes all rows from the Employee and Department tables.
Thanks for reading and make sure to download the source files to get a better understanding of how the code works.
OuterJoins.zip
