Summary: in this tutorial, you will learn how to use the SQL Server
CROSS JOIN to join two or more unrelated tables.
The following illustrates the syntax of SQL Server
CROSS JOIN of two tables:
CROSS JOIN T2;
CROSS JOIN joined every row from the first table (T1) with every row from the second table (T2). In other words, the cross join returns a Cartesian product of rows from both tables.
Suppose the T1 table contains three rows 1, 2, and 3 and the T2 table contains three rows A, B, and C.
CROSS JOIN gets a row from the first table (T1) and then creates a new row for every row in the second table (T2). It then does the same for the next row for in the first table (T1) and so on.
In this illustration, the
CROSS JOIN creates nine rows in total. In general, if the first table has n rows and the second table has m rows, the cross join will result in n x m rows.
CROSS JOIN examples
The following statement returns the combinations of all products and stores. The result set can be used for stocktaking procedure during the month-end and year-end closings:
0 AS quantity
CROSS JOIN sales.stores
Here is the partial output:
The following statement finds the products that have no sales across the stores:
ISNULL(sales, 0) sales
CROSS JOIN production.products p
LEFT JOIN (
SUM (quantity * i.list_price) sales
INNER JOIN sales.order_items i ON i.order_id = o.order_id
INNER JOIN sales.stores s ON s.store_id = o.store_id
INNER JOIN production.products p ON p.product_id = i.product_id
) c ON c.store_id = s.store_id
AND c.product_id = p.product_id
sales IS NULL
The following picture shows the partial result set:
In this tutorial, you have learned how to use the SQL Server
CROSS JOIN to create Cartesian products of rows from the joined tables.