Cross Join in SQL Server

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:

SELECT
    select_list
FROM
    T1
CROSS JOIN T2;

The 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.

Unlike the INNER JOIN or LEFT JOIN, the cross join does not establish a relationship between the joined tables.

Suppose the T1 table contains three rows 1, 2, and 3 and the T2 table contains three rows A, B, and C.

The 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.

SQL Server 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:

SELECT
    product_id,
    product_name,
    store_id,
    0 AS quantity
FROM
    production.products
CROSS JOIN sales.stores
ORDER BY
    product_name,
    store_id;

The following statement finds the products that have no sales across the stores:

SELECT
    s.store_id,
    p.product_id,
    ISNULL(sales, 0) sales
FROM
    sales.stores s
CROSS JOIN production.products p
LEFT JOIN (
    SELECT
        s.store_id,
        p.product_id,
        SUM (quantity * i.list_price) sales
    FROM
        sales.orders o
    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
    GROUP BY
        s.store_id,
        p.product_id
) c ON c.store_id = s.store_id
AND c.product_id = p.product_id
WHERE
    sales IS NULL
ORDER BY
    product_id,
    store_id;