Why/When do we need Cross Join?

Cross Join:

It is a kind of join that returns each and every combination of rows from all the tables. We need not write the where condition for this kind of join. Also most importantly we do not need join condition for doing cross join. It is mandatory to use this join in such a situations like where there will be no relation between the tables but we need to join them.

            Cross join might be scaring most of the developers as it returns all the combinations of data from all the tables which we have included. Also many developers say that avoid using Cross Join while writing SQL queries. They are correct but there will be some situations where we cannot avoid Cross Joins.

For Example:

Let us assume, in a school we have a class of 3 students. Also we have 2 courses to be registered. Each and every student needs to register for all the 2 courses and is mandatory. The combinations are explained below.

Cross-Join

Student 1: Registers to Course A & B. — 2 Records

Student 2: Registers to Course A & B. — 2 Records

Student 3: Registers to Course A & B. — 2 Records

                                                       Total: 6 Records

Please go through the below to understand better.

—————————————-
USE master
GO
—————————————-
CREATE TABLE dbo.Student(
Student_ID Varchar(10)
)
—————————————-
CREATE TABLE dbo.Course(
Course_ID Varchar(10)
)
—————————————-
INSERT INTO dbo.Student
SELECT ‘S1’
UNION
SELECT ‘S2’
UNION
SELECT ‘S3’
—————————————-
INSERT INTO dbo.Course
SELECT ‘C1’
UNION
SELECT ‘C2’
—————————————-

—————————————————
— Using Cross Join Keyword.
SELECT *
FROM dbo.Student
CROSS JOIN dbo.Course

— [OR]

— With out using Cross Join Keyword.
SELECT *
FROM dbo.Student, dbo.Course
————————————————–

Cross-Join-Example

 

Happy Learning :)

Leave a Reply

Your email address will not be published. Required fields are marked *