Skip to main content

SQL to ActiveRecord Examples You Can Paste Into a Rails Console

Real SQL patterns with their closest ActiveRecord equivalents — plus what to verify when Rails generates "almost the same" query.

Most SQL → ActiveRecord mistakes happen because the code "looks equivalent" but the generated SQL is not.

Rule: Convert, then verify:

relation = ...
relation.to_sql

If you're converting a query for production, validate:

  • row count
  • sample IDs
  • duplicates
  • performance (EXPLAIN)

🔍 Deep Dives: Learn the "Why" Behind These Examples

These blog posts explain the common pitfalls and edge cases you'll encounter when converting SQL to ActiveRecord:

LEFT JOIN + WHERE Trap →

Why your LEFT JOIN becomes INNER JOIN

INNER JOIN Conditions →

WHERE vs ON predicate placement

CROSS JOIN Patterns →

Cartesian products without footguns

N+1 Query Shaping →

Performance optimization strategies

ActiveRecord Mental Model →

Why SQL thinking breaks in Rails

All Blog Posts →

Browse all tutorials

Try the Converter

1) Simple SELECT with WHERE 

SELECT * FROM users WHERE active = true;
User.where(active: true)

Verify: Make sure active is a boolean column (not nullable flags that behave differently).

2) WHERE IN 

SELECT * FROM users WHERE id IN (1, 2, 3);
User.where(id: [1, 2, 3])

Verify: Large arrays can create slow queries. Consider joining or subqueries.

3) INNER JOIN with association 

SELECT users.*
FROM users
JOIN posts ON posts.user_id = users.id;
User.joins(:posts)

Verify: This can return duplicate users if a user has multiple posts. If you need unique users, fix the query shape, don't blindly distinct.

4) LEFT JOIN preserving missing rows (common pitfall) 

SELECT users.*
FROM users
LEFT JOIN posts ON posts.user_id = users.id
WHERE posts.id IS NULL;
User.where.missing(:posts)

Verify: This is the safest translation for the "no associated records" pattern. Avoid writing LEFT JOIN + WHERE manually unless you understand WHERE vs ON semantics.

5) EXISTS (semi-join) 

SELECT users.*
FROM users
WHERE EXISTS (
  SELECT 1
  FROM posts
  WHERE posts.user_id = users.id
);
User.where(
  Post.where("posts.user_id = users.id").arel.exists
)

Verify: EXISTS is not the same as joins. Joins can duplicate parent rows and change performance characteristics.

6) GROUP BY + COUNT 

SELECT user_id, COUNT(*) AS posts_count
FROM posts
GROUP BY user_id;
Post.group(:user_id).count

Verify: count returns a hash, not a relation. If you need a relation for sorting/limiting, you'll need select.

7) HAVING 

SELECT user_id, COUNT(*) AS posts_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 5;
Post
  .group(:user_id)
  .having("COUNT(*) > 5")
  .count

Verify: HAVING runs after grouping. Don't replace it with WHERE.

8) ORDER BY + LIMIT 

SELECT *
FROM users
ORDER BY created_at DESC
LIMIT 20;
User.order(created_at: :desc).limit(20)

Verify: For deep pagination, prefer keyset pagination (see /sql-patterns).

9) IS NOT NULL — negated NULL check (common bug) 

SELECT *
FROM subscriptions
WHERE canceled_at IS NOT NULL;
Subscription.where.not(canceled_at: nil)
# Finds canceled subscriptions

❌ Common mistake (inverts logic!):

Subscription.where(canceled_at: nil)
# WRONG: finds ACTIVE subscriptions (opposite meaning!)

This is backwards: canceled_at: nil means "where canceled_at IS NULL", which returns uncanceled subscriptions.

Verify: IS NOT NULL requires .where.not(column: nil). Never use .where(column: nil) for NOT NULL logic — it means the opposite.

10) Complex WHERE with NOT NULL in compound condition 

SELECT *
FROM accounts
WHERE status = 'active'
  AND disabled_at IS NOT NULL;
Account
  .where(status: 'active')
  .where.not(disabled_at: nil)
# Active accounts that have been disabled

❌ Common mistake (wrong semantics!):

Account.where(status: 'active', disabled_at: nil)
# WRONG: finds active accounts that are NOT disabled

This reverses the logic. The SQL says "IS NOT NULL" (disabled) but disabled_at: nil means "IS NULL" (not disabled).

Semantic breakdown:

  • • SQL: "status is 'active' AND disabled_at has a value" → accounts marked as disabled
  • • ActiveRecord (correct): .where.not(disabled_at: nil) → accounts where disabled_at IS NOT NULL
  • • ActiveRecord (wrong): disabled_at: nil → accounts where disabled_at IS NULL

11) Pagination (OFFSET) 

SELECT *
FROM users
ORDER BY id DESC
LIMIT 20 OFFSET 1000;
User.order(id: :desc).limit(20).offset(1000)

Verify: OFFSET gets slower as it grows. Use keyset pagination when possible.

Where to go next

Ready to Convert Your SQL?

Use our free converter to transform any SQL query into ActiveRecord code instantly. Then verify with .to_sql.

Try the Converter → Read the Guide

Free forever • No registration • Instant results

Need More Examples?

Can't find the example you're looking for? Have a specific SQL pattern you need help converting?

Ask a Question Send Feedback Report an Issue