Friday, 29 May 2015

SQL SERVER CARTESIAN JOIN or CROSS JOIN Syntax and Example

SQL Server CARTESIAN Product or CROSS JOIN 
The 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 :

DEPT Table :

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 :



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

--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts