Friday, 29 May 2015

SQL SERVER CARTESIAN JOIN or CROSS JOIN Syntax and Example

SQL CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables.
when a join condition is omitted when getting result from two tables then that kind of query gives us Cartesian product, in which all combination of rows displayed. All rows in the first table is joined to all rows of second table.



Syntax :
Select* from Table_I , Table_2....
OR
Select* from  T1.Column1,T1.Column2, T2.Column3,T2.Column4 from Table_I  T1, Table_2 T2


Example :
Suppose we have a Two Tables as follows:

EMP Table :
EMP_ID
F_Name
L_Name
Dept_ID
Cur_Address
Perm_Address
Salary
1001
Ravi
Kumar
1
Hyderabad
Hyderabad
20000
1002
David
Smith
2
Bangalore
Pune
35000
1003
Victory
Venkatesh
1
Hyderabad
Hyderabad
50000
1004
Tamatam
Reddy
3
Bangalore
Hyderabad
25000
1005
William
Smith
2
Hyderabad
Bangalore
40000
1006
King
Fisher
6
Bangalore
Bangalore
30000

DEPT Table :
Dept_Id
Dept_Name
1
Production
2
Sales
3
Technology
Cartesian or Cross Join Query :
SELECT E_Id,(F_Name+'_'+L_Name) as E_Name , D.Dept_Id,D.Dept_Name 
from EMP E, DEPT D

Output :
As the Table_1(EMP) contains 6 records and Table_2(DEPT) has 3 records , the Cartesian/Cross Join gives an output of 18 (6*3) records as shown below :

E_Id
E_Name
Dept_Id
Dept_Name
1001
Ravi_Kumar
1
Production
1002
David_Smith
1
Production
1003
Victory_Venkatesh
1
Production
1004
Tamatam_Reddy
1
Production
1005
William_Smith
1
Production
1006
King_Fisher
1
Production
1001
Ravi_Kumar
2
Sales
1002
David_Smith
2
Sales
1003
Victory_Venkatesh
2
Sales
1004
Tamatam_Reddy
2
Sales
1005
William_Smith
2
Sales
1006
King_Fisher
2
Sales
1001
Ravi_Kumar
3
Technology
1002
David_Smith
3
Technology
1003
Victory_Venkatesh
3
Technology
1004
Tamatam_Reddy
3
Technology
1005
William_Smith
3
Technology
1006
King_Fisher
3
Technology

Thanks,
TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts