Ask Learn Preview
Please sign in to use this experience.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
A cross join is simply a Cartesian product of the two tables. Using ANSI SQL-89 syntax, you can create a cross join by just leaving off the filter that connects the two tables. Using the ANSI-92 syntax, it’s a little harder; which is good, because in general, a cross join isn't something that you usually want. With the ANSI-92 syntax, it's highly unlikely you'll end up with a cross join accidentally.
To explicitly create a Cartesian product, you use the CROSS JOIN operator.
This operation creates a result set with all possible combinations of input rows:
SELECT <select_list>
FROM table1 AS t1
CROSS JOIN table2 AS t2;
While this result isn't typically a desired output, there are a few practical applications for writing an explicit CROSS JOIN:
When writing queries with CROSS JOIN, consider the following guidelines:
The following query is an example of using CROSS JOIN to create all combinations of employees and products:
SELECT emp.FirstName, prd.Name
FROM HR.Employee AS emp
CROSS JOIN Production.Product AS prd;
Having an issue? We can help!
Please sign in to use this experience.
Sign in