SQL CROSS

What is the SQL CROSS keyword?

CROSS is the SQL keyword that forms a Cartesian product when used in CROSS JOIN or evaluates a table-valued expression per row in CROSS APPLY.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CROSS: CROSS JOIN – PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery. CROSS APPLY – SQL Server, Azure SQL, Oracle, PostgreSQL 14+, MariaDB 10.3+ (as LATERAL), Snowflake (as JOIN LATERAL).

SQL CROSS Full Explanation

CROSS appears in two core constructs:1. CROSS JOIN – Produces a Cartesian product of two tables, pairing every row from the left table with every row from the right table. Because no join condition is supplied, the result set size equals left_row_count × right_row_count.2. CROSS APPLY (supported in SQL Server, Azure SQL, PostgreSQL 14+, Oracle, and others) – Invokes a table-valued function or sub-query for every row of the outer table, returning the concatenated results. It behaves like a LATERAL join in Standard SQL.Key behaviors- Deterministic ordering is not guaranteed unless ORDER BY is used.- Performance degrades rapidly with large tables due to combinatorial growth.- CROSS JOIN is part of the SQL-92 standard; CROSS APPLY is vendor-specific.- A CROSS join is logically equivalent to INNER JOIN without an ON clause.Caveats- Always estimate row counts before running against production data.- Use explicit JOIN keywords to avoid accidental Cartesian products that occur when comma-separated joins omit a WHERE clause.

SQL CROSS Syntax

-- CROSS JOIN (ANSI-SQL)
SELECT column_list
FROM   table1
CROSS JOIN table2;

-- CROSS APPLY (vendor specific)
SELECT t.*, f.*
FROM   table1 AS t
CROSS APPLY dbo.table_valued_func(t.id) AS f;

SQL CROSS Parameters

Example Queries Using SQL CROSS

-- Cartesian product of two small lookup tables
SELECT c.color, s.size
FROM   colors  AS c
CROSS JOIN sizes AS s;

-- Row-by-row evaluation with CROSS APPLY in SQL Server
SELECT p.product_id, x.avg_rating
FROM   products AS p
CROSS APPLY (
        SELECT AVG(rating) AS avg_rating
        FROM   reviews r
        WHERE  r.product_id = p.product_id
) AS x;

Expected Output Using SQL CROSS

First query returns every possible color/size pair
If colors has 5 rows and sizes has 3 rows, output contains 15 rows
Second query returns one row per product augmented with the correlated average rating produced by the APPLY sub-query

Use Cases with SQL CROSS

Generating all combinations of two small dimension tables for permutation testing or lookup tables.
Feeding downstream processes that expect every pairing of two attribute sets.
Replacing nested cursors with CROSS APPLY for cleaner, set-based code that still evaluates a function or sub-query per row.
Performing lateral joins in PostgreSQL or Oracle when writing vendor-neutral code (use CROSS JOIN LATERAL).

Common Mistakes with SQL CROSS

Forgetting that result size explodes quickly, leading to out-of-memory errors.
Assuming CROSS JOIN allows an ON clause; if you need conditions, switch to INNER JOIN.
Misusing the comma-separated join syntax without a WHERE, unintentionally replicating a CROSS JOIN.
Expecting CROSS APPLY to work in MySQL or SQLite (not supported).

First Introduced In

SQL-92 (CROSS JOIN); SQL Server 2005 (CROSS APPLY)

Frequently Asked Questions

What is the difference between CROSS JOIN and INNER JOIN?

An INNER JOIN requires an ON or USING condition to match rows, while a CROSS JOIN has no condition and generates every possible combination of rows.

Does CROSS JOIN guarantee row order?

No. SQL relations are unordered by definition. Add ORDER BY to impose a specific sequence.

Can I filter results after a CROSS JOIN?

Yes. Append a WHERE clause or wrap the join in a sub-query and filter the result set.

Why does my CROSS JOIN query run slowly?

The Cartesian product may be huge. Confirm row counts, add filters, or switch to a conditional join to reduce output rows.