ActiveRecord equivalent of FULL OUTER JOIN

You hit this when you’re doing reconciliation work.

Orders vs payments. Users vs invitations. Subscriptions vs invoices. You want “everything from both sides”, lined up on a shared key, with NULLs filled in where the match doesn’t exist.

In raw SQL you reach for FULL OUTER JOIN and move on.

In Rails, you reach for left_outer_joins… and then notice you’re still missing the “right-only” rows.

This post is about the production-safe ways to do a “full join” in Rails, and the sharp edges that make it feel harder than it should.

The wrong mental model (and why it breaks)

Wrong expectation: “If Rails has left_outer_joins, I should be able to get a FULL OUTER JOIN by chaining / OR-ing something.”

Rails’ query interface gives you:

• joins (INNER JOIN)
• left_outer_joins (LEFT OUTER JOIN) citeturn0search3

…and that’s it. There’s no right_outer_joins, and no full_outer_joins.

So people reach for hacks like:

# naive: this *cannot* return payments that have no matching order
Order
  .left_outer_joins(:payments)
  .where(account_id: account.id)
  .where(payments: { id: nil })

That query is an anti-join (find orders missing payments). Useful, but it’s not “full join”.

The other trap is thinking ActiveRecord’s or is SQL’s UNION. It’s not. or merges WHERE predicates on the same relation (logical union), not result sets. citeturn2search0

The real problem: a FULL OUTER JOIN returns both:

• left rows with no match on the right
• right rows with no match on the left
• plus matched rows

A LEFT OUTER JOIN only guarantees the left side shows up.

Why Rails doesn’t support FULL OUTER JOIN natively

Rails’ public query API tries hard to be portable across the databases it supports (PostgreSQL, MySQL/MariaDB, SQLite, etc.). citeturn2search4

And here’s the uncomfortable truth:

• Not every database Rails supports has FULL OUTER JOIN.
  • PostgreSQL does.
  • SQLite doesn’t.
  • MySQL (still) doesn’t have a real FULL OUTER JOIN operator in the way Postgres does (you simulate it with UNION).

That portability constraint is why the “blessed” API stops at left_outer_joins.

Could Rails expose a Postgres-only feature behind an adapter check? Sure. But Rails usually makes you opt into adapter-specific SQL yourself, because otherwise you ship code that quietly breaks when:

• someone runs test suite on SQLite,
• or a customer is on MySQL,
• or you do a multi-tenant deployment with mixed adapters.

Also: while Arel has nodes that look like they could help (Arel::Nodes::FullOuterJoin exists), Arel is not a stable, public API, and full/right join support has historically been a sore spot. citeturn1search2turn1search14

So Rails ends up in the middle:

• high-level API stays portable
• low-level escape hatch exists (joins("..."), from("..."))
• if you need something fancy, you own the SQL

The correct approaches (pick one based on what you need)

Let’s use a real-ish example.

You have an internal reconciliation report that needs to show:

• orders that never got paid
• payments that don’t map to any order (bad webhook, duplicate provider event, manual refund, etc.)
• matched pairs

Schema-ish:

• orders(account_id, external_id, total_cents, created_at)
• payments(account_id, external_id, amount_cents, created_at)

Option A — Postgres only: write the FULL OUTER JOIN explicitly (safe version)

If you’re on PostgreSQL, the simplest answer is: use SQL’s FULL OUTER JOIN. citeturn0search2

The biggest production mistake is putting filters in WHERE that accidentally “undo” your outer join. PostgreSQL is explicit about this: ON/USING in outer joins is not equivalent to WHERE because outer joins add rows (unmatched rows) as well as filter them. citeturn0search10

Here’s a pattern I’ve used in real apps:

# app/queries/billing/reconciliation_query.rb
class Billing::ReconciliationQuery
  def initialize(account_id:, since:)
    @account_id = account_id
    @since = since
  end

  def call
    Order
      .unscoped # avoid default scopes hiding "missing" rows
      .joins(full_join_sql)
      .select(select_sql)
      .where(time_window_sql, account_id: @account_id, since: @since)
      .order(Arel.sql("external_id ASC NULLS LAST"))
  end

  private

  def full_join_sql
    <<~SQL
      FULL OUTER JOIN payments
        ON payments.account_id = orders.account_id
       AND payments.external_id = orders.external_id
    SQL
  end

  def select_sql
    <<~SQL
      COALESCE(orders.external_id, payments.external_id) AS external_id,
      orders.id   AS order_id,
      payments.id AS payment_id,
      orders.total_cents,
      payments.amount_cents,
      orders.created_at   AS order_created_at,
      payments.created_at AS payment_created_at
    SQL
  end

  # Critical: keep the "include rows from either side" semantics.
  # Don't do `where(orders: { account_id: ... })` or you'll drop right-only rows.
  def time_window_sql
    <<~SQL
      (orders.account_id = :account_id OR payments.account_id = :account_id)
      AND (
        (orders.created_at >= :since OR orders.id IS NULL)
        AND
        (payments.created_at >= :since OR payments.id IS NULL)
      )
    SQL
  end
end

Why this works:

• Join condition stays in ON (keeps outer semantics)
• Filters are written to allow NULL-side rows through (the ... OR <table>.id IS NULL parts)
• unscoped prevents default scopes from hiding “missing” rows (soft delete scopes are the classic footgun)

When not to use it:

• You aren’t on PostgreSQL.
• You need this query to run in dev/test on SQLite without special casing.
• You need a super composable relation and don’t want to carry raw SQL strings.

Option B — Portable workaround: UNION of “left side” + “right-only” (anti-join)

FULL OUTER JOIN is basically:

1. all left rows (with matches if available), plus
2. all right rows that didn’t match anything on the left

You can express that in almost every SQL dialect using UNION (or UNION ALL if you guarantee the halves are disjoint). citeturn0search1turn1search13

In Rails, the cleanest way is to produce one derived table and then query it.

Rails won’t give you Relation#union in core (people typically use a gem), so the pragmatic approach is: build SQL from two relations and run it.

left = Order
  .left_outer_joins(:payments)
  .where(account_id: account.id)
  .select(<<~SQL)
    orders.external_id AS external_id,
    orders.id          AS order_id,
    payments.id        AS payment_id,
    orders.total_cents AS order_total_cents,
    payments.amount_cents AS payment_amount_cents
  SQL

right_only = Payment
  .left_outer_joins(:order) # inverse association
  .where(account_id: account.id)
  .where(orders: { id: nil }) # only payments without a matching order
  .select(<<~SQL)
    payments.external_id AS external_id,
    orders.id            AS order_id,
    payments.id          AS payment_id,
    orders.total_cents   AS order_total_cents,
    payments.amount_cents AS payment_amount_cents
  SQL

sql = <<~SQL
  (#{left.to_sql})
  UNION ALL
  (#{right_only.to_sql})
SQL

rows = ActiveRecord::Base.connection.exec_query(sql).to_a

Why this is production-safe:

• right_only is disjoint by construction (orders.id IS NULL), so UNION ALL won’t duplicate matched rows.
• It works in databases without FULL OUTER JOIN (as long as they support UNION, which most do).

Where people get burned:

• Using UNION instead of UNION ALL “to be safe” and then wondering why it’s slow. UNION performs an implicit deduplication step. citeturn1search16turn1search19
• Selecting orders.*/payments.* with overlapping column names and then mapping the result back to models (you get ambiguous columns or silent overwrites).

If you need AR models out of this, don’t try to “hydrate” directly from the union unless you really know what you’re doing. Usually you want one of:

• a read-only PORO (perfect for reporting endpoints)
• or “fetch IDs first, then load models” (next option)

Option C — You need an ActiveRecord::Relation: use a UNION gem (or load IDs)

If your end goal is “I want to keep chaining scopes/pagination”, you have two realistic options:

1. Use a gem that turns UNION into a relation, like active_record_union. citeturn2search1turn2search5
2. Return a list of IDs from a union query, then query your model normally.

Gem approach is ergonomic, but you’re taking a dependency that:

• may patch internal ActiveRecord behavior,
• may lag Rails releases,
• and may be Postgres-centric in practice.

The “IDs first” approach is boring, but boring is good in production.

Example: if your reconciliation is really “find broken external_ids”, you can select the COALESCE key and then pull records separately.

Production pitfalls (the stuff that bites you at 2am)

1) WHERE clauses can silently turn an outer join into an inner join
Any condition like where(payments: { status: "succeeded" }) will drop right-side NULL rows unless you explicitly allow NULLs through. Postgres calls this out directly. citeturn0search10

2) Duplicates explode when keys aren’t unique
If you “full join” on external_id but you actually have multiple payments per order (retries, partial captures), you’ll create a many-to-many join and multiply rows. Fix by:

• joining on a unique constraint, or
• pre-aggregating (CTE/subquery) to a single row per key, then joining.

3) NULL join keys don’t match
NULL = NULL is not true in SQL. A row with external_id = NULL won’t match the other side’s NULL. If you “fix” this with COALESCE(external_id, 'missing'), you might accidentally match unrelated NULL rows. Prefer to exclude NULL keys from the join key (or treat them as their own category in the report).

4) Default scopes hide the very rows you’re trying to find
Soft-delete default scopes are the classic: the “missing payment” might exist but be soft-deleted, and your “full join” report becomes wrong. Use unscoped in reporting queries unless you’re absolutely sure. (If you can’t, unscope(where: :deleted_at) is a narrower hammer.)

5) Performance: indexes matter more than the join flavor
A FULL OUTER JOIN (or a UNION simulation) still needs to find matches efficiently. In Postgres you typically want a composite index on the join key:

• index_orders_on_account_id_and_external_id
• index_payments_on_account_id_and_external_id

And when in doubt, run EXPLAIN (ANALYZE, BUFFERS) on the exact SQL you generated.

Rule of thumb: if you need FULL OUTER JOIN, you’re usually building a reconciliation report. Keep it read-only, isolate it, and validate results against known “bad” cases before you ship it.

Final thoughts

Rails doesn’t “hide” FULL OUTER JOIN to annoy you. It avoids promising something portable that isn’t.

When you actually need full-join semantics, pick the least magical tool:

• Postgres-only report? Write the FULL OUTER JOIN and be strict about WHERE vs ON.
• Cross-DB app? Simulate it with UNION ALL + a right-only anti-join.
• Need chaining/pagination? Use a UNION gem, or union IDs then load normally.

— To understand activerecord fundamentals, see ActiveRecord Equivalent of SQL CROSS JOIN (And When You Actually Need It).