Webinar Alert : Mastering Manual and Automation Testing! - Reserve Your Free Seat Now
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:
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.
There are three different types of 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.
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
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.
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
The FULL OUTER JOIN keyword returns all records when there is a match in the left (table1) or right (table2) table records.
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
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.
The disadvantage of Outer join in SQL are as follows
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.
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
What is Schema in SQL With Example: All You Need to Know
Data Definition Language (DDL) Commands in SQL
How To Create Database Table-All You Need To know
What does a Database Administrator do? A Detailed Study
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Download Syllabus
Get Complete Course Syllabus
Enroll For Demo Class
It will take less than a minute
Tutorials
Interviews
You must be logged in to post a comment