A Detailed Study on Cross Join in SQL Server

Introduction

Let us introduce you to SQL Join. It is a statement that combines data or rows from two or more tables based on a common field between them. If you're working with databases, at some point in your work, you will likely need to use SQL JOINs and their types. In the upcoming sections, we will explore the concept of cross-joins in SQL Server, exploring their purpose and performance implications.

You can master this field by taking up a course online on sql certification and be a pro in this subject.

What is Cross Join in SQL?

The CROSS JOIN SQL keyword returns all records from tables (table1 and table2). The SQL CROSS JOIN produces a result set, which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called a Cartesian product.

The syntax for SQL Cross Join

The syntax for MS SQL Cross join is as below

SELECT ColumnName_1,
ColumnName_2,
ColumnName_N
FROM [Table_1]
CROSS JOIN [Table_2]

Or we can use the following syntax instead of the previous one. This syntax does not include the CROSS JOIN keyword; only we will place the tables that will be joined after the FROM clause and separated with a comma.

SELECT ColumnName_1,
ColumnName_2,
ColumnName_N
FROM [Table_1],[Table_2]

The resultset does not change for either syntax. In addition, we must notice one point about the CROSS JOIN. Unlike the INNER JOIN, LEFT JOIN, and FULL OUTER JOIN, the CROSS JOIN does not require a joining condition.

When to Use Cross Join

Using the CROSS JOIN query in SQL generates all combinations of records in two tables. For example, you have two columns, size, and color, and you need a result set to display all the possible paired combinations of those—that's where the CROSS JOIN will come in handy.

Advantages of MS SQL Cross Join

Cross Join will be particularly useful when we must select all the possible combinations of rows and columns from both tables.

Disadvantages of Cross Join in SQL Server

MS SQL Cross join is generally not preferred as it takes a lot of time to generate all combinations and produces a considerable result set that is not often useful.

Performance of Cross Join in SQL Server

It will be slow as it will make all possible combinations. MSSQL Cross Join will not be fast until we have either index or inner join.

SQL Cross Join Example

Next, we will discuss a sql server cross join example.We will use the Adventureworks database for our example.

Below is a selection of the ProductVendor table. The select query is as follows:

select * from [Purchasing].[ProductVendor]

The output looks like this below:

Below is a selection of the Product table. The select query is as follows:

select * from [Production].[Product]

The output looks like below

The following SQL statement selects all ProductVendors, and all Products:

SELECT PV.BusinessEntityID,P.Name
FROM [Purchasing].[ProductVendor] PV
CROSS JOIN [Production].[Product] P

The output looks like below

The CROSS JOIN keyword returns all matching records from both tables whether the other table matches. So, if there are rows in "Customers" that do not have matches in "Orders," or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well.

If you add a WHERE clause (if table1 and table2 have a relationship), the CROSS JOIN will produce the same result as the INNER JOIN clause:

SELECT PV.BusinessEntityID,P.Name
FROM [Purchasing].[ProductVendor] PV
CROSS JOIN [Production].[Product] P

Where pv.ProductID=p.ProductID

Conclusion

In the blog, we covered different aspects of Cross Join. We learned about its advantages, disadvantages, and uses. We also discussed how it affects the performance of a database query. I hope this is an exciting read for those who want to learn more about SQL Server Cross Join. If you want to learn but have no time to attend online live classes? Learn SQL concepts at your own pace! Join the SQL Server Self-learning program that will get acquainted with SQL Server programming and give access to a study curriculum prepared by renowned industry experts that covers everything from basics to advanced.

SQL Testing Training

• Personalized Free Consultation
• Access to Our Learning Management System
• Access to Our Course Curriculum
• Be a Part of Our Free Demo Class

Sign Up Now