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:
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:
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:
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:
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:
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:
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:
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:
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: