SQL CROSS JOIN

SQL CROSS JOIN returns a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE  CLAUSE is used along with CROSS JOIN.

It is also known as the Cartesian join since it returns the Cartesian product of the sets of rows from the joined tables. It does not support ON CLAUSE .

SYNTAX

SELECT * FROM TABLE T1 CROSS JOIN TABLE T2

Lets look at an example of CROSS JOIN in SQL SERVER .

CREATE TABLE dbo.DEPARTMENT
(DEPT_ID INT PRIMARY KEY,
DEPT_NAME VARCHAR(50),
DEPT_CODE VARCHAR(15))

INSERT INTO dbo.DEPARTMENT
(DEPT_ID, DEPT_NAME, DEPT_CODE)
VALUES(501, 'IT', '001'),
(502, 'FINANCE', '002'),
(503, 'HR', '003'),
(504, 'MIS', '004'),
(505, 'CALL CENTER', '005'),
(506, 'SALES','006')

CREATE TABLE dbo.EMPLOYEE_INFO 
(EMP_ID INT PRIMARY KEY, 
EMP_NAME VARCHAR(50), 
EMP_CODE VARCHAR(15),
DEPT_ID INT REFERENCES dbo.DEPARTMENT(DEPT_ID)
)  

INSERT INTO dbo.EMPLOYEE_INFO (EMP_ID, EMP_NAME, EMP_CODE, DEPT_ID )
VALUES (1, 'MANISH SINGH', 'EN-0034', 501),
(2, 'MANOJ RAWAT', 'EN-0035', 501),
(3, 'RAJESH KUMAWAT', 'EN-0036', 501),
(4, 'SURAJ K', 'EN-0037', 502),
(5, 'RUPESH SINGH', 'EN-0038', 502),
(6, 'ARJUN PAL', 'EN-0039', 503),
(7, 'GURPREET KAUR', 'EN-0040', 504),
(8, 'TRILOK KUMAR', 'EN-0041', 504),
(9, 'RANUK KHAN', 'EN-0041', null),
(10, 'MANDEEP GREWAL', 'EN-0041', null) 

Here we have two tables one is student and other is student_course as given below .

Following SQL statement returns the cartesian product of rows.

SELECT EMP.EMP_ID, EMP.EMP_NAME, EMP_CODE, DEPT.DEPT_NAME, 
DEPT.DEPT_CODE
FROM dbo.EMPLOYEE_INFO EMP CROSS JOIN dbo.DEPARTMENT DEPT

 

 

As you can see the above output, the number of rows in EMPLOYEE_INFO table is multiplied by number of rows in DEPARTMENT tables that’s why it returns total 60 number of rows. Means, 10 rows of EMPLOYEE_INFO is multiplied with 6 rows of DEPARMENT table.

You should always be careful while choosing cross join in any scenario as it may produce a very large data set.

Also Read..

JOINS OVERVIEW

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN