ActiveRecord Equivalent of SQL CROSS JOIN (And When You Actually Need It)

Start from a real production bug

Someone “optimizes” a report query and ships this:

Order.joins("CROSS JOIN users")

It works in staging.

In production it pins the database: millions of rows multiplied into billions, disk spills start, and your app times out.

CROSS JOIN wasn’t the clever part.

Knowing when you can afford a Cartesian product was.

The wrong mental model

“CROSS JOIN is just another kind of JOIN.”

No. CROSS JOIN is multiplication.

If table A has 10,000 rows and table B has 10,000 rows, a CROSS JOIN produces 100,000,000 rows before you filter anything.

So the first question is never “how do I write it in ActiveRecord?”

It’s:

“Do I really want A×B, or do I want a normal join / EXISTS / grouping?”

When a CROSS JOIN is actually the right tool

CROSS JOIN is usually correct when you’re joining to a small derived set:

• a generated series of dates
• a small set of constants
• a handful of “buckets” you want to report against
• a single-row derived table (basically safe)

Typical SQL example (Postgres):

SELECT day::date, COUNT(*)
FROM generate_series(current_date - 6, current_date, interval '1 day') day
CROSS JOIN orders
WHERE orders.created_at >= day
  AND orders.created_at < day + interval '1 day'
GROUP BY day
ORDER BY day;

Here, the “cartesian” side is only 7 rows. That’s safe.

Why the naive ActiveRecord approach fails

Naive: raw CROSS JOIN string

Order.joins("CROSS JOIN users")

This has three production problems:

1. It’s extremely easy to accidentally join a large table.
2. It encourages bolting conditions on later, after the explosion already happened.
3. It often leads to string-built SQL, which makes bind parameters and refactors harder.

The correct approaches in Rails

Rails doesn’t have a dedicated cross_joins API in ActiveRecord (as of Rails 8.1), so you pick one of these patterns depending on what you’re cross joining.

Pattern 1: CROSS JOIN a small derived set (recommended)

Example: join a small list of “states” or “buckets”

You can CROSS JOIN a VALUES (...) table.

buckets_sql = <<~SQL.squish
  (VALUES ('new'), ('processing'), ('paid')) AS buckets(status)
SQL

rows = Order
  .from("orders CROSS JOIN #{buckets_sql}")
  .where("orders.status = buckets.status")
  .group("buckets.status")
  .count

Typical SQL shape:

SELECT buckets.status, COUNT(*)
FROM orders
CROSS JOIN (VALUES ('new'), ('processing'), ('paid')) AS buckets(status)
WHERE orders.status = buckets.status
GROUP BY buckets.status

Pattern 2: CROSS JOIN with a generated series (reporting)

If you’re on Postgres, generate_series is often the real goal.

days = <<~SQL.squish
  generate_series(current_date - 6, current_date, interval '1 day') AS day
SQL

relation = Order
  .from("orders CROSS JOIN #{days}")
  .where("orders.created_at >= day AND orders.created_at < day + interval '1 day'")
  .group("day")
  .order("day")
  .select("day::date AS day, COUNT(*) AS orders_count")

This is one of the few scenarios where CROSS JOIN is the cleanest tool.

Pattern 3: CROSS JOIN two real tables (rare, but possible)

If you genuinely need A×B (and B is small), do it explicitly:

relation = Order
  .joins("CROSS JOIN users")
  .where("users.id = ?", user_id)
  .select("orders.id, users.email")

What matters is that one side is bounded. orders × 1 user is safe; orders × all users is not.

Production pitfalls & edge cases

1) WHERE conditions don’t save you if the explosion is huge

The planner may still build massive intermediate results. Always estimate row counts.

2) CROSS JOIN is not eager loading

This is SQL shaping, not association loading. If you’re doing it to “avoid N+1”, you’re probably solving the wrong problem.

3) Memory and spill risk

CROSS JOIN often forces sorts/hashes over huge row sets. Watch for:

• work_mem spills
• temp file growth
• timeouts under concurrency

4) Prefer EXISTS for “does a match exist?”

A lot of “cross join then filter” intent is better expressed as EXISTS.

Rule of thumb

If you’re CROSS JOINing two real tables, you’re probably shipping a time bomb.

Use CROSS JOIN when one side is:

• a tiny derived set (VALUES)
• a generated series (dates/buckets)
• a single-row helper

Otherwise, reach for:

• a normal join with an ON condition
• EXISTS
• aggregation with grouping
• precomputed tables/materialized views for reports