What is SQL Server Cross Join?

This tutorial addresses the SQL Server CROSS JOIN condition by the query. We will discuss and conclude several scenarios to help you comprehend the subject better. Below is a list of every subject we’ll cover.

  • What is SQL Server CROSS JOIN condition?
  • How does SQL Server CROSS JOIN work?
  • What is the difference between the INNER JOIN condition and CROSS APPLY operator in SQL Server?
  • What is the difference between the CROSS JOIN and FULL OUTER JOIN conditions on tables in SQL Server?
  • How to use the SQL Server CROSS JOIN conditions on multiple tables?

What is SQL Server CROSS JOIN condition?

Here we will learn and understand how to use the SQL Server CROSS JOIN condition on the tables by the query, which will be explained with the help of syntax and an illustrated example.

In SQL Server, the CROSS JOIN condition is used to join every row from table_1 with every row from table_2. In other words, it returns CARTESIAN PRODUCT of rows from both tables. Just like INNER JOIN or LEFT JOIN, it doesn’t establish a relationship between the joined tables.

Suppose, the CROSS JOIN creates ‘n’ rows in total. But in general, if the table_1 carries N rows and the table_2 carries the M rows then the CROSS JOIN condition will bring the result set of N *M rows.

Here is the syntax of the SQL Server CROSS JOIN condition on the tables by the following query:

SELECT EXPRESSION_1,EXPRESSION_2,EXPRESSION_N
FROM TABLE_1.COLUMN_NAME
CROSS JOIN TABLE_2.COLUMN_NAME;

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT TOP 20 MIT_UNIVERSITY.STUDENT_FIRSTNAME,HARVARD_UNIVERSITY.STUDENT_LASTNAME 
FROM MIT_UNIVERSITY
CROSS JOIN HARVARD_UNIVERSITY;

As we see in the above query, the SELECT statement is used to retrieve all records from the STUDENT_FIRSTNAME column from the MIT_UNIVERSITY table and the records of STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table by using the CROSS JOIN condition in the query.

With the help of the CROSS JOIN condition, it brings all records of both tables based on the cartesian product method.

Whether or not the other table matches, the CROSS JOIN keyword returns all matching records from both tables. Therefore, if there are rows in “MIT_UNIVERSITY” or “HARVARD_UNIVERSITY” that do not have matches in “MIT_UNIVERSITY,” those rows will be shown as well.

In the beginning, we used the TOP 10 clause to bring the first 10 records from both tables.

How does SQL Server CROSS JOIN work example
Example of SQL Server CROSS JOIN condition

We hope that you have understood the definition of the SQL Server CROSS JOIN condition on the tables by the query. For a better understanding, we have used a sample example and explained it in depth.

Read: If Else In Trigger SQL Server

SQL Server CROSS APPLY vs INNER JOIN

Let’s understand the difference between the SQL Server INNER JOIN and CROSS JOIN in detail with the help of syntax and an illustrated example.

In simple terms, the JOIN relies on self-sufficient data which means that sets should be dependent on each other. Records from both tables that match are collected using the SQL Server INNER JOIN condition.

Only those rows from the left table expression that matches the right table expression are returned by the SQL Server CROSS APPLY operator in the final output. In other words, only rows matching the left table expression are produced by the right table expression.

INNER JOIN EXAMPLE:

SELECT USA_STATES.FULL_NAME,USA_STATES.STATE_NAME,
CANADA_STATES.CANADA_STATENAME,
CANADA_STATES.FULL_NAME
FROM USA_STATES
INNER JOIN CANADA_STATES
ON USA_STATES.STATE_ID=CANADA_STATES.STATE_ID;

Here the SELECT statement retrieves all records from FULL_NAME, STATE_NAME and CANADA_STATENAME columns from the USA_STATES and CANADA_STATES tables. Based on the INNER JOIN condition, the common column as STATE_ID column is used with the EQUAL TO operator to make a match with both table expressions for the result set.

Example of SQL Server INNER JOIN condition

CROSS JOIN EXAMPLE:

SELECT * FROM USA_STATES U
CROSS APPLY 
   ( 
   SELECT * FROM CANADA_STATES C
   WHERE U.STATE_ID = C.STATE_ID
   )A;

In this query, the SELECT statement retrieves all records from the USA_STATES based on the CROSS APPLY condition of the subquery. In the subquery, we have used the SELECT statement to retrieve all records from the CANADA_STATES tables based on the WHERE condition.

In the WHERE condition, the common column as STATE_ID column of both tables is connected to each other by the EQUAL TO operator.

In other words, only those rows from the left table expression that match the right table expression are returned by the CROSS APPLY operator in the final output. In other words, only rows matching the left table expression are returned by the right table expression.

Sql server cross apply vs inner join tutorial
Example of SQL Server CROSS APPLY operator

We hope that you have understood the difference between the SQL Server CROSS APPLY and INNER JOIN condition on the tables by the query. For your better understanding, we have used a sample example and defined it in depth.

Read: Recursive Trigger in SQL Server

SQL Server Cross Join vs Full Outer Join

Here, we will learn and understand what is difference between the CROSS JOIN and FULL OUTER JOIN condition on tables by the query, which will be explained with the help of a sample example.

The SQL Server FULL OUTER JOIN condition returns full records from both tables when there is a match of the table_1 and table_2 records.

FULL OUTER JOIN EXAMPLE:

USE sqlserverguides;

SELECT USA_STATES.FULL_NAME,CANADA_STATES.CANADA_STATENAME
FROM USA_STATES
FULL OUTER JOIN CANADA_STATES
ON USA_STATES.STATE_ID=CANADA_STATES.STATE_ID
WHERE USA_STATES.STATE_NAME IS NOT NULL;

In the aforementioned query, the SELECT statement retrieves all records of the FULL_NAME and CANADA_STATENAME columns from the USA_STATES and CANADA_STATES tables by using the FULL OUTER JOIN conditions.

In the FULL OUTER JOINS conditions, it will bring all records from the USA_STATES table and CANADA_STATES table only when the records of the USA_STATES table and the CANADA_STATES tables are met by the ON condition.

Finally, in the WHERE condition, the STATE_NAME column is used with the IS NOT NULL condition to find non-null values from the USA_STATES table. Once the WHERE condition Mets, the SELECT statement will retrieve all records from both tables otherwise vice-versa.

Sql server cross join vs full outer join example
Example of SQL Server FULL OUTER JOIN condition

Example of CROSS JOIN condition:

USE sqlserverguides;

SELECT TOP 10 MIT_UNIVERSITY.STUDENT_FIRSTNAME,
HARVARD_UNIVERSITY.STUDENT_LASTNAME
FROM MIT_UNIVERSITY
CROSS JOIN HARVARD_UNIVERSITY
WHERE MIT_UNIVERSITY.STUDENT_FIRSTNAME like '%a';

In the preceding query, the SELECT statement is used with the TOP 10 and also retrieves all records of the STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from both tables. In the CROSS JOIN condition, it will join every row of both tables with the WHERE condition.

In the WHERE condition, the STUDENT_FIRSTNAME column is used with the LIKE operator to bring the string_name ends with alphabet a from the MIT_UNIVERSITY table. If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from both tables by using the CROSS JOIN condition otherwise vice-versa.

In the beginning, as we see the TOP 10 will bring the first top 10 records of selected columns from both tables by using the SELECT statement.

Example of SQL Server CROSS JOIN condition

We hope that you have understood the subtopic “SQL Server Cross Join vs Full Outer Join” in depth. For understanding the subtopic, we have used a sample example for both JOIN parts and explained it in depth.

Read: SQL Server Trigger to Increment Id

SQL Server Cross Join Multiple Tables

In this MariaDB subtopic tutorial, we will learn and understand how to use the SQL Server CROSS JOIN condition on multiple tables by the query and which will be explained with the help of a sample example.

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT TOP 5 USA_STATES.STATE_ID,CANADA_STATES.CANADA_STATENAME,
STATES_OF_CANADA.CANADA_STATENAME
FROM USA_STATES
CROSS JOIN CANADA_STATES
CROSS JOIN STATES_OF_CANADA;

As we see in the above query the SELECT statement is used with the TOP 5 and retrieve all records of these columns: STATE_ID and CANADA_STATENAME by using the CROSS JOIN condition at the end of the query.

Every row from table_1 is joined with every row from table_2, as well as every row from table_3, using the CROSS JOIN condition in SQL Server. In other words, the CARTESIAN PRODUCT of the rows from all three tables is returned.

In the beginning, the TOP 5 clause refers to bringing the first 5 records from these tables by the query.

Sql server cross join on multiple tables example
Example of SQL Server CROSS JOIN on multiple tables

We hope that you have understood the subtopic “SQL Server Cross Join Multiple Tables” by using the SQL Server CROSS JOIN condition on multiple tables by the query. For your better understanding, we have used a sample example and explained it in depth.

You may also like to read the following SQL Server tutorials.

We now know about the subtopic “What is Cross Join in SQL Server?” after reading this lesson. We also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

  • What is SQL Server CROSS JOIN condition?
  • How does SQL Server CROSS JOIN work?
  • What is the difference between the INNER JOIN condition and CROSS APPLY operator in SQL Server?
  • What is the difference between the CROSS JOIN and FULL OUTER JOIN conditions on tables in SQL Server?
  • How to use the SQL Server CROSS JOIN conditions on multiple tables?