Cross Join in SQL: Understanding the Basics
A cross-join query will return the Cartesian product of rows from the tables in the join. This is the result of combining each row from one table to each row of the second table.
What is Cross Join Used For?
You would use a cross join to view all the possible combinations of the rows of one table along with data from all the rows of a second table. Imagine you wanted to see a list of all the different Tesla SUV models in every color? To make your decision, you need to be able to imagine all the combinations for which a list could be helpful. Because the number of SUV models as colors is small, the result set is not too big. Typically, you would already have a preference for color or model, so you would use a more common join operation that specifies a join condition, resulting in a smaller result set.
What is the Difference Between Union and Cross Join?
The cross join returns every combination of rows of two tables in two columns showing every combination side by side. A union returns the same number of rows in a single column and eliminates duplicates.
Column 1. Column 2
MODEL X black
MODEL Y black
MODEL X white
MODEL Y white
The result with a UNION will look like this:
Column 1
MODEL X
MODEL Y
Black
White
What is the Difference Between Cross Join and Inner Join?
An inner join matches all records between tables but only returns matching values. It is the intersection of two tables. The cross join will display all the rows of both tables.
Cross Join in SQL Syntax
There are two variants in the cross-join SQL syntax, one with and without a comma. Below are examples of both.
The common syntax is: SELECT * FROM [Table1] CROSS JOIN [Table2] ; The comma version of the same statement is: SELECT * FROM [Table1], [Table2] ;
There is no need for a join condition as all rows are returned.
Example
In this example, we will use two tables, one containing Tesla SUV Models as below:
TABLE: Tesla-SUV-Models
Columns: Models
Values:
MODEL X
MODEL Y
The second table contains car colors as below:
TABLE: Tesla-Colors
Columns: Colors
Values:
Black
White
The following is an example SQL statement for a cross join:
SELECT * FROM Tesla-SUV-Models CROSS JOIN Tesla-Colors;
The results will be as follows:
Models Colors
MODEL X black
MODEL Y black
MODEL X white
MODEL Y white
As you can see, the query resulted in returning all the possible combinations of the columns from both tables.
How to Use Cross Joins
The cross join is considered a very expensive statement in terms of data usage because it returns the product of the table being joined. If the first table contains 100 rows and the second has 1000 rows, the resulting cross join query will return 100 x 1000 rows, which is 100,000 rows.
The SQL Server document states that “this is potentially an expensive and dangerous operation since it can lead to a large data explosion. It is best used in scenarios where a normal join cannot be used, and very selective predicates are being used in the WHERE clause to limit the number of produced rows.”
A more format definition of the operation can be described as follows; The Cartesian Product is a multiplication operation in set theory that generates all ordered pairs of the given sets. If A is a set and elements are {a,b} and B is a set with elements {1,2,3}. The Cartesian Product of these two A and B is denoted A x B, and the result will be as follows:
AxB ={(a,1), (a,2), (a,3), (b,1), (b,2), (b,3)}
Many cost-based query optimizers and BI tools will flag the query as expensive to execute, returning an error just at the beginning of the result set as a preview with a warning. If the data does not change often, the tables are big enough that the DBA can create a materialized view of the data, which is automatically refreshed when data changes.
Visit the Actian website to learn more about our range of data products and services.