Monday, February 11, 2013

SQL Server RIGHT Outer JOIN Syntax and Example

SQL Server RIGHT JOIN
The RIGHT JOIN returns all rows from the Right Table (Table_I), includes the matching rows from the Left Table (Table_2).
In other words it returns all the matching rows from both the tables and non matching rows from right table.

Syntax:
SELECT Column_name(s) FROM Table_1 T1 RIGHT JOIN Table_2 T2
ON  T1.Column_name=T2.Column_name
Hint: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

Example :
Suppose we have two tables as follows

The "ORDERS" Table:
The "CUSTOMERS" Table:
Now we want to list all the Orders and the corresponding Customers details.
We use the following Right Join Query :
Select O.Order_Id,C.Cust_Name,C.Cust_Id,C.Cust_Location,
O.Sales_Period,O.Sales 
From ORDERS 
RIGHT JOIN 
CUSTOMERS 
ON C.Cust_Id = O.Cust_Id

Output :

-------------------------------------------------------------------------------------------------------------------
Right Join with Non Match in Left Table :
Right outer join produces a complete set of records from Right Table, with the matching records (where available) in Left Table. If there is no match, the left side will contain null.

The following Right Join Query gives the results only from Right Table that do not have Match in Left Table :
Select O.Order_Id,C.Cust_Name,C.Cust_Id,C.Cust_Location,
O.Sales_Period,O.Sales 
From ORDERS RIGHT JOIN 
CUSTOMERS ON C.Cust_Id = O.Cust_Id  
WHERE O.Cust_Id IS Null
Output :


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog