SQL Server CROSS JOIN

CROSS JOIN produces the result by combining all possibilities of the two or more tables that contain every row from all contributing tables. In other words, it returns the result by generating a paired combination of each row of all tables participating in the join operation. This join is sometimes also termed CARTESIAN JOIN, which provides the Cartesian product of all associated tables.

The Cartesian product can be explained as all rows present in the first table multiplied by all rows present in the second table. It is similar to the INNER JOIN, where the join condition is not available with this clause.

We can understand it with the following visual representation where CROSS JOIN returns all the records from table1 and table2, and each row of the first table combines with each row of the second table.

CROSS JOIN Syntax

The CROSS JOIN keyword must be added after the FROM clause when using the SELECT statement. All records from both joining tables are retrieved using the following syntax:

SELECT column_lists  
FROM table1  
CROSS JOIN table2;  

In the above syntax, the column_lists is the name of the column or field you want to return, and table1 and table2 is the table name from which you fetch the records.

NOTE: The Cross Join in SQL Server cannot use the ON clause with a JOIN condition. However, all other joins can use it with a Join Condition. We will get a syntax error we try to use an ON clause on a CROSS JOIN.

CROSS JOIN Example

Let us understand how CROSS JOIN works in the SQL Server through examples. Suppose we have two tables named "Student" containing student information and "Fee" that contains fee details. Here is the table output:

If we want to fetch all records from both tables, we use the below statement:

SELECT *  
FROM Student  
CROSS JOIN Fee;

After executing the above records, we will get the below output:

When we execute the above statement, we will observe that it displays 40 rows. It means eight rows from the student table multiplied by the five rows from the fee table.

In the above output image, we notice that the table contains several repeated columns. If you want to avoid this repetition, it is recommended to use individual column names instead of the SELECT * statement.

Ambiguous Columns problem in CROSS JOIN

Suppose we want to retrieve only selected columns from all participating tables and these tables contain some column names same. In such a case, SQL Server throws an error: the column name is ambiguous. It signifies that the column's name appears in both tables, and SQL Server is unsure which column you want to retrieve. The following examples can help you understand it better:

SELECT admission_no, first_name, last_name, course, amount_paid  
FROM Student  
CROSS JOIN Fee;  

Executing this statement will produce the following error message:

However, we can resolve this issue by specifying the table name before the column name. Thus, we can re-write the above query as below to remove this error:

SELECT Student.admission_no, Student.first_name, Student.last_name, 
Fee.course, Fee.amount_paid  
FROM Student  
CROSS JOIN Fee;

Executing the query will return the following output:

CROSS JOIN with WHERE clause

We use the WHERE clause for retrieving the filtered rows from the table. The following query will fetch the student records whose admission number is the same in both tables:

SELECT Student.admission_no, Student.first_name, Student.last_name, 
Fee.course, Fee.amount_paid  
FROM Student  
CROSS JOIN Fee
WHERE Student.admission_no = Fee.admission_no;  

Executing the query will return the desired result:

CROSS JOIN with ORDER BY clause

The ORDER BY clause is used to arrange the result of a table in a specified order. The order can be ascending or descending. The following query will display the result in ascending order of the course name:

SELECT Student.admission_no, Student.first_name, Student.last_name, 
Fee.course, Fee.amount_paid  
FROM Student  
CROSS JOIN Fee
WHERE Student.admission_no = Fee.admission_no
ORDER BY course;

Executing the query returns the student information in the ascending order of course name:

CROSS JOIN with multiple tables

The cross join can also work with more than two tables. Here we will learn practically to join more than two tables. We already have two tables named "student" and "fee". Let us create one more table and name it as "contacts" using the following query:

CREATE TABLE contact ( 
 addmission_no INT,
 cellphone BIGINT
)
--insert values
INSERT INTO contact (addmission_no, cellphone)
VALUES (3354, 879373743274), (7555, 788247344374),
(4321, 932167583945), (8245, 987654985632),
(5112, 75758904532);

We can verify the table using the SELECT statement:

Let's execute the following query that joins three tables named student, fee, and contact. In this statement, first CROSS JOIN completed between student and fee, and then it will join with contact table based on the condition specified in the WHERE clause:

SELECT Student.admission_no, Student.first_name, Student.last_name, 
Fee.course, Fee.amount_paid, contact.cellphone
FROM Student  
CROSS JOIN Fee
CROSS JOIN contact
WHERE Student.admission_no = Fee.admission_no AND Fee.admission_no = contact.addmission_no;  

Executing the query return the below output:

Difference between CROSS JOIN and INNER JOIN

It's an important topic regarding the SQL interview questions. The main difference between the cross join and inner join in SQL Server are explained below:

INNER JOIN	CROSS JOIN
This join returns only those records from the tables that match the specified condition and hides other rows and columns. If we have not specified any join, it assumes as a default join.	It returns all possible combinations of the records from both tables. It also returns the Cartesian product of both tables. The Cartesian product means all rows present in the first table multiplied by all rows present in the second table.
This join happens only when the one matched column is found in the tables.	This join does not require matched columns for joining two tables.
It combines the rows that means if two participating tables have 100 records, then the inner join return only 100 records.	It does not combine the rows that means if two participating tables have 100 records, then cross joins return 10000 records.
Its performance is fast as it consumes fewer resources.	Its performance is slow because it consumes more resources.