T-SQL 101: 101 Using CROSS JOIN

The simplest way to join two tables is what’s called a cross-join. So this is where I say from products, cross-join, sales territories.

SELECT *
FROM dbo.Products
CROSS JOIN dbo.SalesTerritories;

Now what that will do though is it will give me every combination of rows from the two tables. So I’ll get one row from the second table for each row in the first table. This is often called a Cartesian product of the two tables and it can quickly end up generating a very large number of rows.

This is exactly the same as if I had just said from DBO products, comma, sales territories.

SELECT *
FROM dbo.Products, dbo.SalesTerritories;

That would be the old way of writing exactly the same query.

If the first table has 60 rows, and the second table has 20 rows, the result would have 1200 rows.

Usually doing this is not particularly useful, but there are occasions where it is exactly what’s intended.

SQL Server Doesn’t Like It

In fact, if you look at the query execution plan for the queries above, you’ll see a warning.

If you hover over the warning icon, you’ll see what it’s concerned about.

SQL Server assumes you have omitted a clause in your query.

I consider that a bug. When I wrote CROSS JOIN, I made it clear what I intended. You can’t have a join predicate in a CROSS JOIN. The problem is that by the time the execution plan is generated, the knowledge that a CROSS JOIN was intended has already been lost.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-01-28