Outer Join in SQL - sql - sql tutorial - learn sql




  • 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;
 full outer join
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; 
 left join
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;
 right join
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

SQL Joins

SQL All Joins
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

All SQL Joins

All SQL Joins

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Outer Join in SQL