SQL Select Basics
Fast recall
Quick revision
Core answer
where filters rows, order defines result order, limit bounds the count, and select restricts columns while still returning model-like records; pluck immediately returns selected scalar values instead. A relation is usually lazy until records are needed, so scopes can compose before one query runs. Values must be passed through hash or placeholder conditions rather than interpolated into SQL.Whether you know the SELECT chain (where/order/limit/select/pluck), relations are lazy, and pluck/find_each for efficiency.
User.all.to_a on a big table (loads everything) — use find_each to batch; or select * when you need a few columns (use select/pluck).
SELECT chain: all/select, where (safe), order, limit, pluck (values), count. Lazy relations. Use find_each to batch big tables.
Interview-ready guide
Interview answer
Active Record represents a SELECT query as a relation that can be built in steps. Methods such as where, order, limit, and select return another relation, so a controller or service can add conditions without immediately loading records.
For example, Order.where(status: :paid).where('total_cents >= ?', 5_000).order(created_at: :desc).limit(20) expresses filtering, safe value binding, deterministic order, and a bounded result. Rails sends the SQL when the relation is enumerated or another loading operation requires the rows.
select(:id, :total_cents) asks for specific columns but returns Order objects with only those attributes loaded. Accessing an unselected attribute can fail. pluck(:id, :total_cents) executes the query and returns simple arrays, avoiding model construction when only values are required.
Conditions supplied through a hash or placeholder keep data separate from SQL syntax and prevent an input value from becoming executable SQL. Raw fragments may still be useful for a database expression, but untrusted values must never be inserted with string interpolation.
I choose columns intentionally, add a stable order before paging, and avoid loading an unbounded table with .all.to_a. For a slow query, I inspect to_sql, logs, and the database execution plan; changing Ruby syntax without checking indexes and row counts may not address the real cost.
SQL Select Basics
Direct Answer
Active Record builds SQL SELECT queries through chainable relations. where filters rows, order defines result order, limit bounds the count, and select restricts columns while still returning model-like records; pluck immediately returns selected scalar values instead. A relation is usually lazy until records are needed, so scopes can compose before one query runs. Values must be passed through hash or placeholder conditions rather than interpolated into SQL.
Deep Dive & Architecture
Relation laziness lets independent layers add constraints. A base scope can express active orders, authorization can restrict account IDs, and a request can add a page limit. If one layer converts the relation to an Array too early, later filtering happens in Ruby and the database loses the chance to optimise the complete query.
Projection changes the result shape. Full model loading is convenient when behaviour and associations are needed. select creates records with a partial attribute set, while pluck returns values without model callbacks or instance methods. The narrowest truthful result saves transfer and allocation but should not surprise later callers.
Ordering is part of a paged query's contract. SQL does not promise row order without ORDER BY; applying limit or offset pagination without a deterministic tie-breaker can return shifting pages. A compound order such as creation time plus ID gives equal timestamps a stable sequence.
The query text is only one layer of performance. Cardinality, indexes, join shape, selected columns, cache state, and database statistics determine the plan. Rails logs and EXPLAIN connect the relation to what the database actually executes.
Code Example
paid_orders = Order
.where(status: :paid)
.where('total_cents >= ?', 5_000)
.order(created_at: :desc, id: :desc)
.limit(20)
# Executes and returns simple arrays rather than Order instances.
summaries = paid_orders.pluck(:id, :total_cents)
# Unsafe: Order.where("email = '#{params[:email]}'")
safe_order = Order.find_by(email: params[:email])
Common Mistakes & Pitfalls
User.all.to_a on a big table (loads everything) — use find_each to batch; or select * when you need a few columns (use select/pluck).
Check your understanding
Answer these without looking back at the article.
- When does an Active Record relation actually execute its SQL?
- How do `select` and `pluck` differ in their returned values?
- Why does paginated SQL need deterministic ordering?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.