7 Production-Safe Ways to Do a SQL `CROSS JOIN` in Rails (and When You Actually Should)

You’re staring at a SQL query that intentionally creates a Cartesian product — and Rails is giving you nothing but joins(:association) and vague vibes.

The typical moment this hits: reporting.

• “Show me every account x month even if there are zero orders.”
• “Build a status matrix for every project and every possible state.”
• “Generate all combinations so the UI can display missing rows as zeros.”

That’s CROSS JOIN.

And it’s also the fastest way to accidentally turn a 10k-row report into a 100M-row incident.

The wrong mental model: “Rails doesn’t support CROSS JOIN because it’s not SQL”

Rails does let you emit almost any join you want — it just doesn’t provide a first-class API for CROSS JOIN.

Why?

ActiveRecord’s join helpers are built around associations (which imply join predicates). A CROSS JOIN is the opposite: no predicate. It’s a blunt tool that’s rarely what you meant, and it’s dangerously easy to blow up row counts.

Rails’ official docs explicitly support passing raw SQL join fragments to joins, but it doesn’t try to model every SQL join type as a method. citeturn1search15turn1search0

So the Rails answer is: “If you really want it, you can write it.”

Why the naive solution fails: joins is not “all joins”

If you try this:

Account.joins(:months)

…you’ve already lost. There is no months association. And even if you build one, an association implies a join predicate (ON ...) — which is not what a cross join is.

Or you go down the Arel rabbit hole and define Arel::Nodes::CrossJoin… and it explodes with a visitor error (Cannot visit Arel::Nodes::CrossJoin). That’s because Arel’s internal visitors don’t magically understand new nodes you invent. citeturn1search1

So the production-safe approach is usually:

1. Write the join fragment you need, and
2. Keep it parameterized/sanitized, and
3. Constrain the right side hard to avoid row explosions.

The correct approach: express CROSS JOIN in Rails (without making it a footgun)

1) The boring solution: pass a CROSS JOIN string to joins

Rails Guides explicitly document string SQL fragments in joins. citeturn1search15

Account
  .joins("CROSS JOIN statuses")
  .where(statuses: { active: true })

This is totally valid SQL, and Rails will happily compose it.

But: if statuses has 50 rows and accounts has 200k rows, you just created a 10M-row intermediate result.

Rule #1 of cross joins: make the right side small.

2) CROSS JOIN with a derived table (subquery) you control

When you need “all combinations”, you almost never want “all combinations of everything”. You want combinations of a small dimension: a handful of statuses, a date range, a fixed set.

In Postgres you can materialize that dimension as a derived table and cross join it.

Here’s the canonical SQL for “account x day” using generate_series (Postgres):

SELECT accounts.id, days.day
FROM accounts
CROSS JOIN generate_series('2026-01-01'::date, '2026-01-07'::date, interval '1 day') AS days(day);

Postgres documents CROSS JOIN as the join type that produces the Cartesian product. citeturn1search2

Rails version:

from = Date.new(2026, 1, 1)
to   = Date.new(2026, 1, 7)

days_sql = ActiveRecord::Base.send(
  :sanitize_sql_array,
  ["CROSS JOIN generate_series(?::date, ?::date, interval '1 day') AS days(day)", from, to]
)

Account
  .select("accounts.id, days.day")
  .joins(days_sql)

Why sanitize here? Because generate_series parameters are not identifiers — they’re values — and you want Rails to quote them correctly.

3) The one that surprises people: CROSS JOIN LATERAL (per-row series)

If you try to do per-row series (e.g., each account has its own date window) you’ll hit “missing FROM-clause entry” unless you use LATERAL.

This is the subtle point:

• CROSS JOIN <subquery> cannot reference the left table.
• CROSS JOIN LATERAL <subquery> can.

Rails still won’t help you. You still pass SQL.

Example shape:

series_sql = <<~SQL
  CROSS JOIN LATERAL generate_series(
    accounts.starts_on,
    accounts.ends_on,
    interval '1 day'
  ) AS days(day)
SQL

Account
  .where.not(starts_on: nil, ends_on: nil)
  .select("accounts.id, days.day")
  .joins(series_sql)

Pitfall: if starts_on or ends_on is NULL, generate_series returns no rows, and you’ll silently drop the account. If you need “accounts even when dates are missing”, you’re not doing a cross join anymore — you’re doing outer joins + defaults.

4) When you really meant INNER JOIN: push the predicate into ON

A classic misuse is:

“I used CROSS JOIN because there’s no association.”

But your SQL immediately adds WHERE a.id = b.account_id.

That’s not a cross join. That’s an inner join.

This:

FROM accounts
CROSS JOIN orders
WHERE orders.account_id = accounts.id

is equivalent to:

FROM accounts
INNER JOIN orders ON orders.account_id = accounts.id

Prefer the explicit ON predicate. It’s clearer, and optimizers reason about it better.

In Rails, use a string join with ON, or build the join with Arel if you want AST composition (with the caveat that Arel is effectively a private API). citeturn1search9turn0search2

Production pitfalls (the stuff that breaks real apps)

Row explosion is not hypothetical

A cross join multiplies cardinalities. If you cross join 200k accounts with 30 days, that’s 6M rows before filters.

• Put a hard cap on ranges (date windows, status lists).
• Always sanity-check the row count you’re about to generate.

Ambiguous columns + SELECT *

If both sides have id, created_at, etc., SELECT * makes debugging miserable and can break consumers that expect stable columns.

Be explicit:

• select("accounts.id AS account_id, days.day")
• Or alias with AS.

Default scopes can “leak” into the dimension

If Status has default_scope { where(active: true) }, your “all statuses” report will be missing rows and you’ll spend an hour staring at SQL.

For dimension tables, I usually do:

• Status.unscoped in the subquery, or
• materialize the dimension explicitly (values list / CTE).

DISTINCT is a band-aid with a bill

Once you cross join and then join other tables, duplicates happen.

Your first instinct will be distinct.

That can turn a linear query into a sort/hash-heavy query that drags. If you need uniqueness, try to model the row you want (e.g., group + aggregate), not “generate a bunch of rows then dedupe them.”

Indexes still matter

Cross joins don’t use indexes by themselves, but the filters you apply afterward do.

If you cross join and then filter by orders.account_id + date range, you want an index that matches the access pattern ((account_id, created_at) is a common winner).

Debugging: how to prove you’re not about to DOS yourself

• Print the SQL: relation.to_sql
• Run it with EXPLAIN (ANALYZE, BUFFERS) in psql for the actual plan.
• Watch for:
  • giant row estimates,
  • HashAggregate / Sort from DISTINCT,
  • sequential scans on large tables when you expected index scans.

Rails won’t stop you from shipping a cross join. Your database will.

Rule of thumb: If the right side of a CROSS JOIN is not “obviously small” (dozens, not thousands), you’re probably building a time bomb. citeturn1search2

Final thoughts

Rails doesn’t “lack” CROSS JOIN — it chooses not to make it easy to misuse. When you truly need it (reporting grids, time series, matrices), use joins with a constrained derived table and keep the row counts brutally bounded. To understand activerecord fundamentals, see ActiveRecord Equivalent of SQL CROSS JOIN (And When You Actually Need It).