If you have developed computer programs or compiled data into database at any point, you must be aware of the standard normalization procedure of relational database management system, i.e. you wouldnt have saved all the data in a single table.As an illustration, take the sales register. Any application typically stores sales data in the sales header or sales detail files. Even though each table has a primary key and a foreign key to help you connect them, extracting data from them requires a specific method or tool. This procedure or tool goes by the name Joins. There are different types of sql joins used for their respective purposes. In this blog, we will explain what is outer join in SQL and SQL outer join examples:
What are Outer Join in SQL?
Outer join in SQL are joins that return matched values and unmatched values from either or both tables. There are a few types of outer joins: LEFT JOIN returns only unmatched rows from the left table and matched rows in both tables. The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
What are The Different Types of Outer Joins in SQL
There are three different types of outer join in SQL:
- Left outer join.
- Right outer join.
- Full outer join.
Left Outer Join in SQL
A left outer join is a method of combining tables. The result includes unmatched rows from the table specified before the LEFT OUTER JOIN clause. If you join two tables and want the result to include unmatched rows from only one table, use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause. Let's dive into MSSQL outer join and learn more about their importance in SQL and key takeaways. You should check out the SQL career path guide to help you explore all your career options.
Use of Left Outer Join
The LEFT JOIN command returns all rows from the left table and the matching rows from the right table. The result is NULL from the right side if there is no match.
Now let us discuss the syntax of Left Outer Join.
Table 1 would be considered a left table, and Table 2 would be a right table. Table1.column1, Table1.column2, Table2.column1, is the name of the columns which you want to retrieve, separated by a comma.
SELECT Table1.column1, Table1.column2, Table2.column1, Table2.column2...
FROM Table1
LEFT JOIN Table2
ON
Table1.matchingcolumnname=Table2.matchingcolumnname
Let us now consider two tables [Sales].[SalesOrderDetail] and [Sales].[SalesOrderHeader]
The query to view the data from SalesOrderHeader is below.
select * from [Sales].[SalesOrderHeader]
The Dataset Looks Like Below:
The query to view the data from SalesOrderDetail looks like below.
select * from [Sales].[SalesOrderDetail]
The Dataset Looks Like Below:
The query for Left Outer Join using SalesOrderHeader and SalesOrderDetail looks like below.
select
slh.SalesOrderID,
slh.OrderDate,
slh.DueDate,
sld.OrderQty
from [Sales].[SalesOrderHeader] slh
left outer join
[Sales].[SalesOrderDetail] sld
on slh.SalesOrderID=sld.SalesOrderID
The Output Looks Like Below
Right Outer Join in SQL
The RIGHT OUTER JOIN keyword returns all records from the right table (table2) and the matching records from the left table (table1). The result is 0 records from the left side if there is no match.
Use of Right Outer Join in SQL
A right outer join is a method of combining tables. The result includes unmatched rows from only the table specified after the RIGHT OUTER JOIN clause. If you join two tables and want the result to include unmatched rows from only one table, use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause.
The syntax for the right outer join looks like below:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Let us now consider two tables [Sales].[SalesOrderDetail] and [Sales].[SalesOrderHeader]
The query to view the data from SalesOrderHeader is below.
select * from [Sales].[SalesOrderHeader]
The Dataset Looks Like Below:
The query to view the data from SalesOrderDetail looks like below.
select * from [Sales].[SalesOrderDetail]
The Dataset Looks Like Below:
The query for Right Outer Join using SalesOrderHeader and SalesOrderDetail looks like below.
select
slh.SalesOrderID,
slh.OrderDate,
slh.DueDate,
sld.OrderQty
from [Sales].[SalesOrderHeader] slh
Right outer join
[Sales].[SalesOrderDetail] sld
on slh.SalesOrderID=sld.SalesOrderID
The Output Looks Like Below
Full Outer Join in SQL
The FULL OUTER JOIN keyword returns all records when there is a match in the left (table1) or right (table2) table records.
Use of Full Outer Join in SQL
A full outer join combines tables to include unmatched rows of both tables. If you join two tables and want the result to include unmatched rows from both tables, use a FULL OUTER JOIN clause. The matching is based on the join condition.
The syntax for full outer join is like below
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
Let us now consider two tables [Sales].[SalesOrderDetail] and [Sales].[SalesOrderHeader]
The query to view the data from SalesOrderHeader is below.
select * from [Sales].[SalesOrderHeader]
The Dataset Looks Like Below:
The query to view the data from SalesOrderDetail looks like below.
select * from [Sales].[SalesOrderDetail]
The Dataset Looks Like Below:
The query for Full Outer Join using SalesOrderHeader and SalesOrderDetail looks like below.
select
slh.SalesOrderID,
slh.OrderDate,
slh.DueDate,
sld.OrderQty
from [Sales].[SalesOrderHeader] slh
full outer join
[Sales].[SalesOrderDetail] sld
on slh.SalesOrderID=sld.SalesOrderID
The Output Looks Like Below
Advantage of Outer Join In SQL
An outer join returns results by combining rows from two or more tables. But unlike an inner join, the outer join will return every row from one specified table, even if the join condition fails.
Disadvantage of Outer Join In SQL
The disadvantage of Outer join in SQL are as follows
- The WHERE clause of OUTER JOIN can only have a '=' relational operator. <,>, etc is not allowed in case of OUTER JOIN of two or more tables. ...
- The functions to handle NULL operators, such as VALUE and COALESCE, could not be used with the OUTER JOINS.
Performance of Outer Join in SQL
An outer join will not normally give you A multiplied by B as the number of results, as it isn't a union. It will give you the number of records in A or B as a result, depending on the primary selection of the two. Outer join only means that you want all records on the primary selected table and null values in the joined table's columns when there is no corresponding record in the joined table. A*B would only occur if you can do a "full" outer join: a left and right outer join combined.
Conclusion
In the above write-up, we have discussed different types of outer join. We have discussed left out advantages, and disadvantages. Hope this can become a learning step for all who want to study more about outer join. Understanding the SQL joins begins with understanding SQL Server; you can get an insight about the same through our online SQL server training.
SQL Training For Administrators & Developers
- No cost for a Demo Class
- Industry Expert as your Trainer
- Available as per your schedule
- Customer Support Available
Upcoming Class : November 3
