Cross Join

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.

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

SELECT
 select_list
FROM
 T1
 CROSS JOIN T2;

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

Sources:

SQL Server Cross Join