Monday, February 11, 2013

SQL Server FULL Outer JOIN Syntax and Example

SQL Server FULL JOIN 
The FULL JOIN is an outer join that returns all matching and non matching rows from both the tables.
Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null. 

Syntax :
SELECT Column_name(s) FROM Table_1 T1 FULL JOIN Table_2 T2
ON  T1.Column_name=T2.Column_name

Example:
Suppose we have two tables as follows
The "ORDERS" Table:
The "CUSTOMERS" Table:
Now we want to list all the Customers and their Orders, and all the Orders with their Customers.We use the following SELECT statement:
Select O.Order_Id,C.Cust_Name,C.Cust_Id,C.Cust_Location,
O.Sales_Period,O.Sales 
from ORDERS 
FULL JOIN 
CUSTOMERS 
ON O.Cust_Id = C.Cust_Id

Output :
-------------------------------------------------------------------------------------------------------------------
Full  Join with Non Match in Both the Tables :
Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null. 

The following Full Join Query gives the results as follows :
Select O.Order_Id,C.Cust_Name,C.Cust_Id,C.Cust_Location,
O.Sales_Period,O.Sales 
from ORDERS 
FULL JOIN 
CUSTOMERS 
ON O.Cust_Id = C.Cust_Id
WHERE O.Cust_Id IS Null OR C.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