What is a CROSS JOIN?

A CROSS JOIN combines two or more tables together, so every combination of records between the two tables that are combined in a CROSS JOIN will appear in the result set. When we talk about combining multiple tables, we mean merging the data from each individual table into a single table.

SQL CROSS JOIN Syntax

SELECT
 a.column1
,b.column2
FROM Table1 a, Table2 b

Example of using the CROSS JOIN for Test Data

A CROSS JOIN will help you if you need a lot of data. You have a table of products and vendors. There are some products that the vendor offers. If it’s empty and you need data quickly, here’s what you can do:

SELECT
 P.ProductID
,v.BusinessEntityID AS VendorID
FROM 
Production.Product p
CROSS JOIN Purchasing.Vendor v

In our copy, it generated over 50 000 records. It’s enough to test apps and performance. If you present your app to users, use your source instead of this data.

Use CROSS JOIN to Join Three Tables

Cross Join is a SQL statement that allows you to combine two different sets of data into one set. This can be used to create a single table from multiple sources of information. Let’s look at the example. We need to get all the combinations of car models and colors, as well as the tyres that go with them.

Example:

SELECT
	 c.Car_model
	,c1.Color_name
	,t.Tyre_manufacturer
FROM Cars c
CROSS JOIN Colors c1
CROSS JOIN Tyres t

And here is the results set consisting of 27 rows. There are 3 car models, 3 car colors, and now we add 3 tyre manufacturers.

The CROSS JOIN is a powerful SQL statement that will return all the possible combinations of the values in the three tables:

  • Product
  • ProductCategory
  • ProductAttribute

Using CROSS JOIN to Generate a Report

CROSS JOIN is an SQL query that allows you to combine two sets of data into one result set. This is done by using the Cross Product operator. For example, suppose you have the following tables:

  • Table 1 contains the names of all the students in school.
  • Table 2 lists the grades for each student. The table below shows the results when you use the Cross Join to create a list of the names of the students and their grade point averages.

You can also use the CROSS JOIN to generate a report. Suppose you want to know how many students received A’s and B’s in Math class this year. You could do so with the following code:

SELECT COUNT(*) AS NumA, SUM(Grade) as Total 
FROM Student, Grade 
WHERE Student.StudentID = Grade.StudentID AND Grade.Year=2009 
GROUP BY Student.StudentID;

In addition to generating a single row of data, you can also use the Cross Join to generate multiple rows.

Use CROSS JOIN to Generate a Large Set of Data

Big data sets can be generated by cross joints, so they should be used wisely. The Color_names and Car_models columns have 1000 rows each. When we cross join them in a query, it will return a result that is one million rows.

Many experts recommend avoiding CROSS JOINS because of their performance issues. If a CROSS JOIN is really needed, the best option is to pre-aggregate data. Use the GROUP BY clause to pre-aggregate data if you want to avoid performance issues with CROSS JOINs.