SQL Server CROSS JOIN: Cartesian Product Examples (vs LEFT JOIN and CROSS APPLY)

Three stores times four months gave me exactly 12 rows. A SQL Server CROSS JOIN pairs every row of one table with every row of the other: a x b rows, no ON clause.

SELECT s.Store, m.MonthStart FROM dbo.Stores s CROSS JOIN dbo.Months m; I used it to find each store’s zero-sales months. Here’s what we’re building:

Before You Start: Stores, Months and Sales

Test setup: SQL Server 17.0.1000.7 Standard Developer edition, sqlcmd in VS Code. Months holds first-of-month dates; Ridgeview has no sales:

CREATE TABLE dbo.Stores (StoreID int PRIMARY KEY,
    Store nvarchar(12) NOT NULL);
CREATE TABLE dbo.Months (MonthStart date PRIMARY KEY);
CREATE TABLE dbo.Sales (SaleID int PRIMARY KEY, StoreID int NOT NULL,
    SaleDate date NOT NULL, Amount decimal(8,2) NOT NULL);

INSERT INTO dbo.Stores VALUES (1, N'Maple Street'),
    (2, N'Harbor Point'), (3, N'Ridgeview');
INSERT INTO dbo.Months VALUES ('2026-01-01'), ('2026-02-01'),
    ('2026-03-01'), ('2026-04-01');
INSERT INTO dbo.Sales VALUES (1, 1, '2026-01-14', 420.00),
    (2, 1, '2026-01-28', 180.50), (3, 1, '2026-03-09', 615.25),
    (4, 2, '2026-02-03', 299.99), (5, 2, '2026-04-17', 150.00);

Step 1: Build the Store x Month Grid With CROSS JOIN

SET NOCOUNT ON;
SELECT (SELECT COUNT(*) FROM dbo.Stores) AS Stores,
       (SELECT COUNT(*) FROM dbo.Months) AS Months,
       COUNT(*) AS GridRows
FROM dbo.Stores CROSS JOIN dbo.Months;

Output:

Stores      Months      GridRows   
----------- ----------- -----------
          3           4          12

What this tells me: the old comma form, FROM dbo.Stores, dbo.Months, gives 12 too.

Checkpoint: GridRows is 12.

Step 2: LEFT JOIN Sales to the Grid for Zero-Sales Months

The grid is the left side of a LEFT JOIN, so nothing drops:

SET NOCOUNT ON;
SELECT s.Store, CONVERT(char(7), m.MonthStart, 120) AS Month,
       CAST(ISNULL(SUM(x.Amount), 0) AS decimal(8,2)) AS Sales
FROM dbo.Stores AS s
CROSS JOIN dbo.Months AS m
LEFT JOIN dbo.Sales AS x
    ON x.StoreID = s.StoreID
   AND x.SaleDate >= m.MonthStart
   AND x.SaleDate < DATEADD(month, 1, m.MonthStart)
GROUP BY s.StoreID, s.Store, m.MonthStart
ORDER BY s.StoreID, m.MonthStart;

Output:

Store        Month   Sales     
------------ ------- ----------
Maple Street 2026-01     600.50
Maple Street 2026-02        .00
Maple Street 2026-03     615.25
Maple Street 2026-04        .00
Harbor Point 2026-01        .00
Harbor Point 2026-02     299.99
Harbor Point 2026-03        .00
Harbor Point 2026-04     150.00
Ridgeview    2026-01        .00
Ridgeview    2026-02        .00
Ridgeview    2026-03        .00
Ridgeview    2026-04        .00

Ridgeview shows .00 (sqlcmd drops the zero) four times. A plain join can’t produce those rows; same idea as a LEFT JOIN with COUNT.

Checkpoint: 12 rows; Maple Street has 600.50 in January.

Step 3: Estimate the Cartesian Product Before Running It

On my box, sys.all_columns x sys.all_objects is 35,393,020 rows. Only counting them took 34 ms (SYSDATETIME before and after).

Test the Grid: Does CROSS JOIN With WHERE Become an INNER JOIN?

Yes. Plan hashes from the cache:

SET NOCOUNT ON;
SELECT * FROM dbo.Stores s JOIN dbo.Sales x
    ON x.StoreID = s.StoreID; /* grid-inner */
GO
SELECT * FROM dbo.Stores s CROSS JOIN dbo.Sales x
    WHERE x.StoreID = s.StoreID; /* grid-cross */
GO
SELECT * FROM dbo.Stores s, dbo.Sales x
    WHERE x.StoreID = s.StoreID; /* grid-comma */
GO
SELECT DISTINCT CAST(SUBSTRING(t.text, CHARINDEX('grid-', t.text), 10)
    AS char(10)) AS Query, q.query_plan_hash
FROM sys.dm_exec_query_stats q CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) t
WHERE t.text LIKE '%/* grid-%' AND t.text NOT LIKE '%dm_exec%';

Output:

Query      query_plan_hash   
---------- ------------------
grid-comma 0x0DDD69463A0B9D4E
grid-cross 0x0DDD69463A0B9D4E
grid-inner 0x0DDD69463A0B9D4E

One hash: the INNER JOIN plan. Write INNER JOIN anyway. It says what you mean.

CROSS JOIN vs LEFT OUTER JOIN, INNER JOIN and FULL OUTER JOIN

SET NOCOUNT ON;
SELECT
 (SELECT COUNT(*) FROM dbo.Stores CROSS JOIN dbo.Sales) AS CrossJ,
 (SELECT COUNT(*) FROM dbo.Stores s JOIN dbo.Sales x
     ON x.StoreID = s.StoreID) AS InnerJ,
 (SELECT COUNT(*) FROM dbo.Stores s LEFT JOIN dbo.Sales x
     ON x.StoreID = s.StoreID) AS LeftJ,
 (SELECT COUNT(*) FROM dbo.Stores s FULL JOIN dbo.Sales x
     ON x.StoreID = s.StoreID) AS FullJ;

Output:

CrossJ      InnerJ      LeftJ       FullJ      
----------- ----------- ----------- -----------
         15           5           6           6

CROSS is 3 x 5. INNER keeps 5 matched pairs (an equi-join). LEFT OUTER adds Ridgeview with NULLs; FULL OUTER ties it, since every sale has a store.

CROSS APPLY vs CROSS JOIN (and OUTER APPLY)

CROSS JOIN can’t see the left row; APPLY can, so it gets each store’s top sale:

SET NOCOUNT ON;
SELECT s.Store, t.Amount
FROM dbo.Stores AS s
CROSS APPLY (SELECT TOP (1) x.Amount
             FROM dbo.Sales AS x WHERE x.StoreID = s.StoreID
             ORDER BY x.Amount DESC) AS t;

SELECT s.Store, t.Amount
FROM dbo.Stores AS s
OUTER APPLY (SELECT TOP (1) x.Amount
             FROM dbo.Sales AS x WHERE x.StoreID = s.StoreID
             ORDER BY x.Amount DESC) AS t;

Output:

Store        Amount    
------------ ----------
Maple Street     615.25
Harbor Point     299.99
Store        Amount    
------------ ----------
Maple Street     615.25
Harbor Point     299.99
Ridgeview          NULL

The right side can also be an inline table-valued function taking s.StoreID.

Variation: Size x Color Product Variants

SET NOCOUNT ON;
SELECT CONCAT('TEE-', z.Size, '-', c.Color) AS SKU
FROM (VALUES ('S'), ('M'), ('L')) AS z(Size)
CROSS JOIN (VALUES ('NAVY'), ('SAND')) AS c(Color);

Output:

SKU       
----------
TEE-S-NAVY
TEE-M-NAVY
TEE-L-NAVY
TEE-S-SAND
TEE-M-SAND
TEE-L-SAND

Troubleshooting: Error 156 When You Add ON to a CROSS JOIN

SET NOCOUNT ON;
BEGIN TRY
    EXEC (N'SELECT s.Store FROM dbo.Stores AS s
            CROSS JOIN dbo.Sales AS x ON x.StoreID = s.StoreID;');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS Error,
           CAST(ERROR_MESSAGE() AS varchar(40)) AS Message;
END CATCH;

Output:

Error       Message                                 
----------- ----------------------------------------
        156 Incorrect syntax near the keyword 'ON'.

Important: needing ON means you wanted INNER or LEFT JOIN.

My Takeaways on SQL Server CROSS JOIN

1. Rows = a x b. Check first.
2. Build grids, then LEFT JOIN facts.
3. Correlated? Use APPLY.

Where to Take It Next

PostgreSQL runs the same CROSS JOIN (joins in PostgreSQL) and uses LATERAL for APPLY.

More SQL Server joins:

• Self join a table
• INNER JOIN on multiple tables
• DISTINCT in a JOIN

Frequently Asked Questions