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.
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.
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.
CREATE TABLE dbo.Student(
CREATE TABLE dbo.Course(
INSERT INTO dbo.Student
INSERT INTO dbo.Course
— Using Cross Join Keyword.
CROSS JOIN dbo.Course
— With out using Cross Join Keyword.
FROM dbo.Student, dbo.Course