Monday, 11 February 2013

SQL Server - FULL Outer JOIN Syntax and Example

SQL 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 LEFT JOIN Table_2 T2
ON  T1.Column_name=T2.Column_name

Example:
Suppose we have two tables as follows

The "ORDERS" Table:
Order_Id
Sales_Period
Cust_Id
Sales
ABC_1234
Q1-2014
Cust_1234
40474
BCD_2345
Q2-2014
Cust_2345
44321
CDE_3456
Q3-2014
Cust_3456
19906
DEF_4567
Q4-2014
Cust_4567
34943
EFG_1122
Q1-2015
Cust_1122
22114
FGH_3344
Q2-2015
Cust_3344
41364
GHI_5566
Q3-2015
Cust_5566
16157
IJK_7788
Q4-2015
Cust_7788
36054

The "CUSTOMERS" Table:
Cust_Id
Cust_Name
Cust_Location
Cust_1234
ABC Soft Pvt 
Ltd
East
Cust_3456
CDE Info Pvt 
Ltd
West
Cust_4466
FFDD Info Pvt Ltd
Mid-West
Cust_1122
EFC Tech Pvt Ltd
South
Cust_6688
HHFF Tech Pvt Ltd
Mid-South
Cust_5566
GHI Services Pvt Ltd
North



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 LEFT JOIN CUSTOMERS ON O.Cust_Id = C.Cust_Id

Output :
Order_Id
Cust_Name
Cust_Id
Cust_Location
Sales_Period
Sales
ABC_1234
ABC Soft Pvt Ltd.
Cust_1234
East
Q1-2014
40474
BCD_2345
NULL
NULL
NULL
Q2-2014
44321
CDE_3456
CDE Info Pvt Ltd
Cust_3456
West
Q3-2014
19906
DEF_4567
NULL
NULL
NULL
Q4-2014
34943
EFG_1122
EFC Tech Pvt Ltd.
Cust_1122
South
Q1-2015
22114
FGH_3344
NULL
NULL
NULL
Q2-2015
41364
GHI_5566
GHI Services Pvt Ltd.
Cust_5566
North
Q3-2015
16157
IJK_7788
NULL
NULL
NULL
Q4-2015
36054
NULL
FFDD Infor Pvt Ltd.
Cust_4466
Mid-West
NULL
NULL
NULL
HHFF Tech Pvt Ltd.
Cust_6688
Mid-South
NULL
NULL

-------------------------------------------------------------------------------------------------------------------
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 LEFT JOIN CUSTOMERS ON O.Cust_Id = C.Cust_Id
WHERE O.Cust_Id IS Null OR C.Cust_Id IS Null
 
Output :
Order_Id
Cust_Name
Cust_Id
Cust_Location
Sales_Period
Sales
BCD_2345
NULL
NULL
NULL
Q2-2014
44321
DEF_4567
NULL
NULL
NULL
Q4-2014
34943
FGH_3344
NULL
NULL
NULL
Q2-2015
41364
IJK_7788
NULL
NULL
NULL
Q4-2015
36054
NULL
FFDD Infor Pvt Ltd.
Cust_4466
Mid-West
NULL
NULL
NULL
HHFF Tech Pvt Ltd.
Cust_6688
Mid-South
NULL
NULL

Thanks,
TAMATAM
       [ BI-Reporting Analyst ]


No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts