skip to content

In Active Record, how do includes, preload and eager_load differ, and when does includes switch to a single LEFT OUTER JOIN?

level: seniorimportance: must knowfreq 62%

answer

  1. preload: always separate queries
  2. eager_load: LEFT OUTER JOIN, t0_r0 aliases
  3. includes decides per relation
  4. references takes table names
  5. polymorphic belongs_to cannot be joined

basics

~20 s

preload always runs a separate IN-list query per association; eager_load always loads through a LEFT OUTER JOIN; includes behaves like preload until a where hash, order, references call or matching joins needs the associated table.

solid answer

~40 s

`preload` runs one extra query per association, so you cannot filter on the associated table: a `where(customers: ...)` fails because `customers` is not in the main FROM clause. `eager_load` folds every association into one `LEFT OUTER JOIN` statement with aliased columns (`t0_r0`, `t1_r0`), which lets you filter and sort on the joined table. `includes` picks for you: separate queries by default, a join when `eager_loading?` becomes true, which happens when a `where` hash or an `order` names the association's table, when `references(:customers)` is called (needed for SQL-string conditions, and it takes the table name), or when the same association is also passed to `joins`. Choose `preload` for polymorphic `belongs_to` (joining one raises `ActiveRecord::EagerLoadPolymorphicError`) and for wide has_many fan-out; choose `eager_load` when you filter on the association.

code

ruby · 8 lines
ruby
Order.includes(:customer).eager_loading?
# => false (separate queries)

Order.includes(:customer).where(customers: { vip: true }).eager_loading?
# => true (the where hash references customers)

Order.includes(:customer).where("customers.vip = ?", true).eager_loading?
# => false, and the query fails until you add .references(:customers)

go deeper

for a junior

Recall the three names and the two strategies: separate IN queries or one LEFT OUTER JOIN, with includes choosing between them.

for a middle

Explain exactly what flips includes into a join: a where hash or order naming the table, references, or the same association in joins.

for a senior

Justify the choice per query: preload for polymorphic targets and wide collections, eager_load when filtering on the association, and how limits change the SQL.

for a principal

Set a team rule that keeps loader choice reviewable, such as explicit preload or eager_load in hot paths, rather than relying on includes' silent switch.

## Three loaders, two strategies Active Record has two ways to load associations up front, and three methods that choose between them: | Method | Strategy | Can filter on the associated table? | |---|---|---| | `preload` | One extra `SELECT ... WHERE key IN (...)` per association | No: the associated table is not in the main query | | `eager_load` | A `LEFT OUTER JOIN` per association in one statement (plus an id query under a limit) | Yes: the joined table is in the FROM clause | | `includes` | `preload` by default, `eager_load` when the query needs the table | Yes, once it has switched to a join | All three take the same arguments: symbols, arrays and nested hashes such as `line_items: :wine`. ## What eager_load actually emits `Order.eager_load(:customer)` runs a single statement that selects every column of both tables under generated aliases, `"orders"."id" AS t0_r0, ..., "customers"."id" AS t1_r0`, and Active Record rebuilds the orders and their customers from the joined rows. Two details matter in interviews: - **Fan-out.** With a `has_many` such as `line_items`, every order row repeats once per item, so a wide collection multiplies the bytes the database sends. - **Limits.** `Order.eager_load(:line_items).limit(20)` cannot put `LIMIT 20` on the joined rows, because that would cut an order's items in half. Active Record first runs a `SELECT DISTINCT` of order ids with the limit, then the joined statement restricted to those ids. A collection join plus a limit is therefore two statements. ## When includes switches to a join `Relation#eager_loading?` is the switch, and it returns true when any of these holds: 1. `eager_load` was called; 2. a `where` **hash** names the associated table, as in `includes(:customer).where(customers: { vip: true })`, because the hash records a table reference for you; 3. an `order` names the table, as in `order("customers.name")`; 4. `references(:customers)` was called; 5. the same association appears in both `includes` and `joins`. A **SQL string** in `where` is not parsed for table names. `Order.includes(:customer).where("customers.vip = ?", true)` keeps separate queries, so the database rejects the unknown `customers` table and the adapter raises `ActiveRecord::StatementInvalid`. Adding `.references(:customers)` fixes it. Note the asymmetry: `includes` takes **association names** (`:customer`), while `references` takes **table names** (`:customers`). ## Choosing deliberately - **`preload`** when you only need the data: the IN queries stay narrow and avoid join fan-out. It is also the only loader that handles a **polymorphic `belongs_to`**, say `Review belongs_to :reviewable, polymorphic: true` pointing at wines or producers, because there is no single table to join; `eager_load(:reviewable)` raises `ActiveRecord::EagerLoadPolymorphicError`. - **`eager_load`** when the query filters or sorts on the associated table and you want the result loaded in the same statement. - **`includes`** as the default in application code, as long as you know which mode a relation will land in. Calling `eager_loading?` on the relation in a console shows the answer. - **`joins` plus `preload`** when you need to filter on the association but want the full collection loaded; the join filters, the preload loads everything (the next question's trap). ## Telling them apart from the SQL When a relation surprises you, the statements it emits show which strategy ran: - **Separate `SELECT ... WHERE ... IN (...)` statements** after the main query mean preload mode, whether you wrote `preload` or `includes` stayed in its default. - **One statement with `t0_r0`, `t1_r0` column aliases** and `LEFT OUTER JOIN` means eager-load mode. - **A `SELECT DISTINCT` of ids first** means an eager load of a collection under a limit or offset. The cost profiles differ too. Preload mode sends several small, index-friendly statements and builds no duplicate rows; eager-load mode makes one round trip but repeats parent columns per child row, and each extra collection in the same join multiplies the row count again. Neither is faster in general: measure the page. ## A worked comparison ```ruby Order.preload(:customer).where(customers: { vip: true }) # => ActiveRecord::StatementInvalid: no "customers" in FROM Order.eager_load(:customer).where(customers: { vip: true }) # => one statement, LEFT OUTER JOIN customers ... WHERE customers.vip = TRUE Order.includes(:customer).where("customers.vip = ?", true).references(:customers) # => same single statement as eager_load ``` The senior answer names the two strategies, the conditions under which `includes` flips, the table-name rule for `references`, and at least one reason to force `preload` (polymorphic targets or collection fan-out).

  • Why does Order.eager_load(:line_items).limit(20) issue two statements?
    A limit on joined rows would truncate an order's items, so Active Record first selects the DISTINCT ids of 20 orders with the limit applied, then runs the LEFT OUTER JOIN restricted to those ids. With a belongs_to or has_one join the row count already matches the orders, so no extra statement is needed.
  • Reviews belong to a polymorphic reviewable. Which loader works?
    `preload(:reviewable)`, or `includes` while it stays in separate-query mode. Active Record groups the reviews by `reviewable_type` and runs one IN query per target table. `eager_load` raises `ActiveRecord::EagerLoadPolymorphicError`, because a join needs one known table and a polymorphic association has several.

  • preload and eager_load are aliases for includes
  • includes always builds a LEFT OUTER JOIN
  • references takes the association name, like includes
  • includes parses SQL string conditions and adds the join itself
  • eager_load works on a polymorphic belongs_to
  • eager_load with limit caps the joined rows, not the parent records