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 & Analytics Professional
--------------------------------------------------------------------------------------------------------