Cyber Monday Deal : Flat 30% OFF! + free self-paced courses - SCHEDULE CALL
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:
