SQL Server Brain Basher of the Week #033 - Cartesian Product

SQL statements produce various types of results and forming the result is based on the way the query has been written. This week brain basher is based on types of results that SQL statement produces;

What is a Cartesian Product and how it can be produced?

There are different types of JOINs that can be used for combining tables such as INNER JOIN, LEFT OUTER JOIN, FULL OUTER JOIN. In addition to these well known JOINs, SQL Server facilitates CROSS JOIN that has only one logical query processing phase. This requires two tables as inputs for the JOIN and produces a Cartesian Product of the two tables.

Cartesian Product (referred as Cross Product and Cross Join as well) is an operator that works on two sets. This combines two records (tuples); records (tuples) from one table (relation) will all the records (tuples) from other table (relation).

There are two ways of writing CROSS JOIN with SQL Server; ANSI SQL-92 and ANSI SQL-89. There are no performance differences between these two, both works fine but ANSI SQL-92 is the most common one.

USE AdventureWorks2014;
GO

/* Producing a resultset for
product catgories for each year
*/

-- ANSI SQL-92
SELECT 
 DISTINCT YEAR(OrderDate) Year
 , Name Category
FROM Sales.SalesOrderHeader
CROSS JOIN Production.ProductCategory
ORDER BY 1, 2;

-- ANSI SQL-89
SELECT 
 DISTINCT YEAR(OrderDate) Year
 , Name Category
FROM Sales.SalesOrderHeader, Production.ProductCategory
ORDER BY 1, 2;