In the SQL outer JOIN all the content of the both tables are integrated together either they are matched or not.
The syntax for performing an outer join in SQL is database-dependent. For example, in Oracle, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows.
Let's assume that we have the following two tables, and we want to find out the sales amount for all of the stores.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
The syntax of outer join is:
SELECT column_name(s)
FROM table1
OUTER JOIN table2 ON table1.column_name = table2.column_name;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Outer join of two types:
Left outer join
Right outer join
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
1.Left outer join (also known as left join):
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2).
The result is NULL from the right side, if there is no match.
Syntax for left join:
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Example:
Consider a join of the Product table and the ProductReview table on their ProductID columns. The results show only the products for which reviews have been written.
To include all products, regardless of whether a review has been written for one, use an ISO left outer join. The following is the query:
USE AdventureWorks2008R2;
GO
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID
The LEFT OUTER JOIN includes all rows in the Product table in the results, whether or not there is a match on the ProductID column in the ProductReview table.
Notice that in the results where there is no matching product review ID for a product, the row contains a null value in the ProductReviewID column.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
2.Right outer join (also known as right join):
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1).
The result is NULL from the left side, when there is no match.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Syntax for Right join:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
Examples:
Consider a join SalesTerritory table and the SalesPerson table on their TerritoryID columns. The results show any territory that has been assigned to a sales person.
The ISO right outer join operator, RIGHT OUTER JOIN, indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.
To include all sales persons in the results, regardless of whether they are assigned a territory, use an ISO right outer join. Here is the Transact-SQL query and results of the right outer join:
USE AdventureWorks2008R2;
GO
SELECT st.Name AS Territory, sp.BusinessEntityID
FROM Sales.SalesTerritory st
RIGHT OUTER JOIN Sales.SalesPerson sp
ON st.TerritoryID = sp.TerritoryID ;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial
