Day: February 18, 2024

Today we move from basic filtering and aggregation into query composition.

This is an important topic for senior Rails interviews because many real production queries can be expressed in several ways:

JOIN
IN
EXISTS
Subquery
ActiveRecord association queries

A senior engineer should know not only how to write them, but also:

Which query expresses the business requirement most clearly?

Does the query preserve duplicates?

Does NULL affect the result?

Does PostgreSQL need to calculate one value or evaluate rows repeatedly?

What SQL is ActiveRecord generating?

We’ll build on the users and orders tables from Day 4.

Today’s Goals

By the end of Day 5, you should understand:

• What a subquery is
• Scalar subqueries
• Multi-row subqueries
• Subqueries in WHERE
• Subqueries in FROM
• Correlated subqueries
• IN
• EXISTS
• NOT EXISTS
• NOT IN and the NULL trap
• ANY
• ALL
• JOIN vs IN vs EXISTS
• ActiveRecord equivalents
• Common mistakes
• Senior interview questions

Part 1 – Prepare the Practice Data

We’ll use the same domain from Day 4, but add a few more rows to make today’s queries more interesting.

First, inspect your current data:

SELECT * FROM users ORDER BY id;
SELECT * FROM orders ORDER BY id;

If you want to recreate everything from scratch, run:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100)
);
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    amount NUMERIC(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
);

Insert users:

INSERT INTO users(name, city)
VALUES
('John', 'New York'),
('Mary', 'Chicago'),
('Bob', 'Chicago'),
('Alice', 'Boston'),
('David', 'Mumbai'),
('Sara', 'Boston');

Insert orders:

INSERT INTO orders(user_id, amount, status)
VALUES
(1, 100, 'completed'),
(1, 250, 'completed'),
(1, 75,  'pending'),
(2, 500, 'completed'),
(2, 300, 'completed'),
(3, 200, 'pending'),
(4, 800, 'completed'),
(4, 150, 'cancelled'),
(6, 1000, 'completed'),
(6, 1200, 'completed');

Now our data looks conceptually like this:

This dataset is intentionally designed so we can practice:

• users with orders
• users without orders
• users above average spending
• users with expensive orders
• correlated subqueries
• EXISTS and NOT EXISTS

Part 2 – What Is a Subquery?

A subquery is a query nested inside another SQL statement.

Example:

SELECT *
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

The inner query is:

SELECT AVG(amount)
FROM orders;

The outer query is:

SELECT *
FROM orders
WHERE amount > (...);

Conceptually:

Inner query
    ↓
Calculate average order amount
    ↓
Return the result
    ↓
Outer query
    ↓
Find orders greater than that value

Let’s run the inner query separately first:

SELECT AVG(amount)
FROM orders;

Total amount:

4575

Number of orders:

10

Average:

457.5

Now the outer query becomes conceptually:

SELECT *
FROM orders
WHERE amount > 457.5;

Result:

500
800
1000
1200

Rails Equivalent

Order.where(
  "amount > (?)",
  Order.select("AVG(amount)")
)

However, in Rails you may also see:

average = Order.average(:amount)
Order.where("amount > ?", average)

These are not exactly the same approach.

The first can produce one SQL statement containing a subquery.

The second executes:

Query 1 → Calculate average
Query 2 → Find orders above average

That distinction can matter when data changes between queries and when minimizing database round trips.

Part 3 – Scalar Subqueries

A scalar subquery returns:

One row
One column

Therefore, it produces a single value.

Example:

SELECT AVG(amount)
FROM orders;

Result:

457.5

We can use that result with operators such as:

=
>
<
>=
<=
<>

Example:

SELECT
    id,
    user_id,
    amount
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

What Happens if the Subquery Returns Multiple Rows?

Try:

SELECT *
FROM orders
WHERE amount = (
    SELECT amount
    FROM orders
);

The inner query returns many rows.

PostgreSQL will raise an error similar to:

more than one row returned by a subquery used as an expression

Why?

Because:

amount = ???

expects one value.

But the subquery returned:

100
250
75
500
300
...

PostgreSQL cannot compare one amount against multiple scalar values using =.

This leads us to IN.

Part 4 – IN with a Subquery

Suppose the requirement is:

Find users who have placed at least one order.

We can write:

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
);

Run the subquery separately:

SELECT user_id
FROM orders;

Result:

1
1
1
2
2
3
4
4
6
6

Conceptually:

SELECT *
FROM users
WHERE id IN (1, 1, 1, 2, 2, 3, 4, 4, 6, 6);

Result:

John
Mary
Bob
Alice
Sara

David is excluded because he has no orders.

Rails Equivalent

A good ActiveRecord version is:

User.where(
  id: Order.select(:user_id)
)

Conceptually, Rails can generate:

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
);

Notice the difference between:

Order.select(:user_id)

and:

Order.pluck(:user_id)

This is important.

Using select

User.where(id: Order.select(:user_id))

can remain a single SQL statement with a subquery.

Using pluck

User.where(id: Order.pluck(:user_id))

executes the inner query immediately.

Conceptually:

Query 1
SELECT user_id FROM orders;

Then Rails constructs another query:

Query 2
SELECT *
FROM users
WHERE id IN (1, 1, 1, 2, 2, 3, ...);

For a large dataset, that can be undesirable.

Senior-Level Insight

When building SQL subqueries in ActiveRecord, don’t automatically reach for pluck.

Ask:

Do I want Ruby to materialize these IDs?

Or:

Can PostgreSQL keep the work inside one SQL statement?

Part 5 – NOT IN

Suppose the requirement is:

Find users who have never placed an order.

You might write:

SELECT *
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
);

Result:

David

With our current schema, this works because:

orders.user_id BIGINT NOT NULL

Therefore, the subquery cannot return NULL.

But NOT IN has a famous SQL trap.

Part 6 – The NOT IN + NULL Trap

Let’s create a small demonstration table.

DROP TABLE IF EXISTS order_users_demo;
CREATE TABLE order_users_demo (
    user_id BIGINT
);

Insert:

INSERT INTO order_users_demo(user_id)
VALUES
(1),
(2),
(NULL);

Now run:

SELECT *
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM order_users_demo
);

You might expect:

Bob
Alice
David
Sara

But you get:

0 rows

Why?

Because SQL uses three-valued logic:

TRUE
FALSE
UNKNOWN

Conceptually:

id NOT IN (1, 2, NULL)

behaves like:

id <> 1
AND id <> 2
AND id <> NULL

But:

id <> NULL

is not TRUE.

It is:

UNKNOWN

And:

TRUE AND TRUE AND UNKNOWN

results in:

UNKNOWN

WHERE only keeps rows where the condition evaluates to TRUE.

This is one of the most important SQL interview traps to remember.

Part 7 – EXISTS

Now let’s solve:

Find users who have at least one order.

Using EXISTS:

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Result:

John
Mary
Bob
Alice
Sara

David is excluded.

How Does EXISTS Work?

Look carefully:

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

The inner query references:

u.id

But u was defined in the outer query.

Therefore, this is a:

Correlated Subquery

Conceptually PostgreSQL evaluates:

John
    ↓
Does an order exist with user_id = John's id?
    ↓
Yes
    ↓
Keep John

Then:

Mary
    ↓
Does an order exist?
    ↓
Yes
    ↓
Keep Mary

Then:

David
    ↓
Does an order exist?
    ↓
No
    ↓
Remove David

Important: this is a useful conceptual model, but it does not mean PostgreSQL must literally execute the inner query once per outer row. The optimizer can transform correlated EXISTS queries into efficient semi-join plans.

We’ll inspect that later using:

EXPLAIN ANALYZE

Why SELECT 1?

You commonly see:

EXISTS (
    SELECT 1
    FROM orders
    ...
)

Why 1?

Because EXISTS doesn’t care what columns are returned.

It only asks:

Does at least one matching row exist?

These are semantically equivalent:

EXISTS (
    SELECT 1
    FROM orders
    WHERE ...
)

EXISTS (
    SELECT *
    FROM orders
    WHERE ...
)

EXISTS (
    SELECT amount
    FROM orders
    WHERE ...
)

SELECT 1 communicates intent clearly.

Part 8 – NOT EXISTS

Requirement:

Find users who have never placed an order.

SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Result:

David

This is called an anti-join pattern.

Conceptually:

For each user:
Does a matching order exist?
YES → reject
NO → keep

Compare With LEFT JOIN

We learned this yesterday:

SELECT u.*
FROM users u
LEFT JOIN orders o
ON o.user_id = u.id
WHERE o.id IS NULL;

And today:

SELECT u.*
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Both express:

Find users without orders.

The PostgreSQL optimizer may produce similar execution strategies.

However, NOT EXISTS often expresses the business requirement more directly:

Keep the user if no matching order exists.

Rails Equivalent: where.missing

Rails provides a very readable API:

User.where.missing(:orders)

Conceptually, Rails generates a LEFT OUTER JOIN with an IS NULL condition.

Another option is to build a NOT EXISTS query using Arel, but for standard Rails association queries, where.missing is usually clearer.

Rails Equivalent: where.associated

Find users who have orders:

User.where.associated(:orders)

Depending on Rails version and query construction, this uses an association join and filters out missing related rows.

You may also write:

User.joins(:orders).distinct

Remember why distinct can be needed:

John has 3 orders
JOIN result:
John
John
John

EXISTS does not duplicate John because it tests existence rather than returning matching order rows.

This is a major conceptual difference.

Part 9 – IN vs EXISTS

Let’s compare them.

IN

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
);

Conceptually:

Is this user’s ID present in the set of order user IDs?

EXISTS

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Conceptually:

Does at least one matching order exist for this user?

Which Is Faster?

A common junior-level answer is:

EXISTS is always faster.

That’s incorrect.

Modern PostgreSQL can rewrite IN and EXISTS into similar plans, such as semi-joins.

Performance depends on:

• table sizes
• indexes
• statistics
• data distribution
• selectivity
• query structure
• PostgreSQL planner decisions

The correct senior-level approach is:

Choose the query that expresses the requirement clearly, then inspect the execution plan when performance matters.

Later we’ll compare:

EXPLAIN ANALYZE
SELECT ...
WHERE id IN (...);

with:

EXPLAIN ANALYZE
SELECT ...
WHERE EXISTS (...);

Part 10 – Correlated Subqueries

We’ve already seen one:

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

The inner query depends on the outer query.

Let’s look at another important example.

Requirement:

Find orders whose amount is greater than the average order amount for that particular user.

This is different from:

Find orders above the global average.

Global average:

SELECT *
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

Per-user average:

SELECT
    o.id,
    o.user_id,
    o.amount
FROM orders o
WHERE o.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.user_id = o.user_id
);

Notice:

o2.user_id = o.user_id

The inner query references the outer row.

Let’s manually reason through John.

John’s orders:

100
250
75

Average:

141.67

Which John’s orders are above John’s average?

250

Mary:

500
300

Average:

400

Above average:

500

Alice:

800
150

Average:

475

Above average:

800

Sara:

1000
1200

Average:

1100

Above average:

1200

Bob has only one order:

200

Average:

200

Condition:

200 > 200

False.

So Bob has no matching result.

Rails Equivalent

A direct SQL fragment is often the clearest ActiveRecord solution:

Order.where(<<~SQL)
  orders.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.user_id = orders.user_id
  )
SQL

Senior Rails Insight:

Not every query should be forced into a chain of ActiveRecord methods.

For complex database logic, readable SQL inside ActiveRecord can be better than complicated Arel code.

The important things are:

• parameterize external input
• keep the SQL understandable
• test it
• inspect its execution plan when needed

Part 11 – Subqueries in FROM

A subquery can also act like a temporary result set.

Requirement:

Calculate each user’s total spending, then return only users whose total spending exceeds 500.

First calculate totals:

SELECT
    user_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY user_id;

Now use that result as a derived table:

SELECT *
FROM (
    SELECT
        user_id,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY user_id
) user_totals
WHERE total_spent > 500;

Important:

PostgreSQL requires an alias for the derived table:

user_totals

Conceptually:

orders
↓
GROUP BY user_id
↓
temporary result set
user_id | total_spent
↓
filter temporary result
↓
total_spent > 500

Of course, for this particular query, HAVING is simpler:

SELECT
    user_id,
    SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 500;

So why learn subqueries in FROM?

Because derived tables become useful when:

• aggregating in multiple stages
• joining against aggregated results
• ranking data
• reporting queries
• building complex analytical queries

Part 12 – ANY

ANY compares a value against values returned by a subquery.

Example:

SELECT *
FROM orders
WHERE amount > ANY (
    SELECT amount
    FROM orders
    WHERE user_id = 1
);

John’s order amounts:

100
250
75

The condition is:

amount > ANY (100, 250, 75)

This means:

The amount must be greater than at least one value.

Effectively:

amount > 75

because being greater than the smallest value is enough to satisfy the condition.

Therefore:

> ANY

can often be thought of as:

Greater than at least one value

Part 13 – ALL

Now:

SELECT *
FROM orders
WHERE amount > ALL (
    SELECT amount
    FROM orders
    WHERE user_id = 1
);

John’s amounts:

100
250
75

Condition:

amount > ALL (100, 250, 75)

The amount must be greater than every value.

Effectively:

amount > 250

Therefore:

> ALL

means:

Greater than every value returned by the subquery.

Important ANY / ALL Mental Model

Given:

10
20
30

Then:

value > ANY (10, 20, 30)

means:

value > at least one of them

Equivalent threshold:

value > 10

But:

value > ALL (10, 20, 30)

means:

value > every one of them

Equivalent threshold:

value > 30

Be careful: this shortcut depends on the comparison operator. For example, < ANY and < ALL have different effective thresholds.

Part 14 – ANY with ActiveRecord Arrays

You may occasionally see PostgreSQL queries like:

SELECT *
FROM users
WHERE id = ANY(ARRAY[1, 2, 3]);

However, normal Rails code would usually use:

User.where(id: [1, 2, 3])

which generates an IN condition.

Don’t use PostgreSQL-specific syntax unless it provides a real advantage.

Part 15 – JOIN vs IN vs EXISTS

Requirement:

Find users who have completed orders.

JOIN

SELECT DISTINCT u.*
FROM users u
JOIN orders o
ON o.user_id = u.id
WHERE o.status = 'completed';

Potential issue:

The join produces one row per matching order.

Therefore, duplicates may occur.

We use:

DISTINCT

IN

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE status = 'completed'
);

No duplicate users in the outer result.

EXISTS

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.status = 'completed'
);

Also no duplicate users.

How Should You Choose?

Use JOIN when:

You need columns from both tables.
You need to aggregate related rows.
You intentionally need matching rows.

Use EXISTS when:

You're asking whether a related row exists.
You don't need columns from the related table.
You want existence semantics without row multiplication.

Use IN when:

You're checking membership in a set of values.
The query reads naturally as "value belongs to this result set."

Do not choose solely based on old rules such as:

EXISTS is always faster than IN.

PostgreSQL’s optimizer is smarter than that.

Part 16 – Practical PostgreSQL Exercises

Let’s practice one query at a time.

Exercise 1

Find all orders above the global average order amount.

SELECT *
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

Rails:

Order.where(
  "amount > (?)",
  Order.select("AVG(amount)")
)

Exercise 2

Find users who have orders.

SQL using IN:

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
);

Rails:

User.where(id: Order.select(:user_id))

Exercise 3

Find users who have orders using EXISTS.

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Rails association-oriented alternative:

User.where.associated(:orders)

or:

User.joins(:orders).distinct

Exercise 4

Find users without orders.

SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Rails:

User.where.missing(:orders)

Exercise 5

Find users who have at least one completed order greater than 400.

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.status = 'completed'
      AND o.amount > 400
);

Try writing the ActiveRecord version yourself before looking below.

One option:

User
  .joins(:orders)
  .where(orders: { status: "completed" })
  .where("orders.amount > ?", 400)
  .distinct

Exercise 6

Find orders above the average order amount for that order’s user.

SELECT *
FROM orders o
WHERE o.amount > (
    SELECT AVG(o2.amount)
    FROM orders o2
    WHERE o2.user_id = o.user_id
);

This is today’s most important correlated subquery exercise.

Run it and manually verify every returned row.

Part 17 – Common Mistakes

Mistake 1: Using = with a Multi-Row Subquery

Wrong:

WHERE id = (
    SELECT user_id
    FROM orders
)

If multiple rows are returned, PostgreSQL raises an error.

Use:

IN

or:

EXISTS

depending on the requirement.

Mistake 2: Using NOT IN Without Considering NULL

Potentially dangerous:

WHERE id NOT IN (
    SELECT user_id
    FROM some_table
)

If the subquery can return NULL, the result may surprise you.

Safer existence-oriented query:

WHERE NOT EXISTS (...)

Mistake 3: Using pluck When You Want a SQL Subquery

Potentially inefficient:

User.where(id: Order.pluck(:user_id))

Better:

User.where(id: Order.select(:user_id))

when you want PostgreSQL to handle the operation as a subquery.

Mistake 4: Using JOIN + DISTINCT for Every Existence Check

User.joins(:orders).distinct

works.

But if your requirement is simply:

Does a matching row exist?

EXISTS more directly expresses the requirement.

Mistake 5: Assuming a Correlated Subquery Always Executes Once Per Row

Conceptually, we reason about it that way.

Physically, PostgreSQL may optimize it into:

• Semi Join
• Anti Join
• Hash Join
• Nested Loop
• other execution strategies

Always distinguish:

SQL semantics

from:

physical execution plan

This distinction is very important for senior-level interviews.

Part 18 – Senior Interview Questions

Try answering these without looking back.

Q1

What is the difference between a normal subquery and a correlated subquery?

Q2

What happens if a scalar subquery returns multiple rows?

Q3

What’s the difference between:

Order.select(:user_id)

and:

Order.pluck(:user_id)

when used to build another query?

Q4

Why can NOT IN return zero rows when the subquery contains NULL?

Q5

What’s the difference between:

JOIN

and:

EXISTS

when one user has many matching orders?

Q6

Is EXISTS always faster than IN in PostgreSQL?

Q7

What is a semi-join?

Q8

What is an anti-join?

Q9

When would you use a subquery in the FROM clause instead of HAVING?

Q10

What is the difference between:

> ANY

and:

> ALL

Part 19 – Today’s Interview Challenge

Do not run this immediately.

First predict the result.

SELECT
    u.id,
    u.name
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.amount > (
          SELECT AVG(o2.amount)
          FROM orders o2
          WHERE o2.user_id = u.id
      )
);

Questions:

1. What does the innermost query calculate?
2. Is the innermost query correlated?
3. What does the middle EXISTS query check?
4. Which users will be returned?
5. Will a user with exactly one order be returned?
6. Why does this query not need DISTINCT?

Try to manually execute it for:

John
Mary
Bob
Alice
David
Sara

If you can reason through this query confidently, your understanding of SQL has moved beyond basic CRUD querying.

Homework

Use the users and orders tables.

Write both SQL and ActiveRecord for each exercise.

1. Find users who have at least one completed order.
2. Find users who have no completed orders.
3. Find orders greater than the global average order amount.
4. Find orders greater than the average order amount for their respective user.
5. Find users whose total order amount is greater than the average total spending across all users who have orders.
6. Find users who have an order greater than every order placed by John. Use ALL.
7. Find users who have an order greater than at least one order placed by Sara. Use ANY.
8. Rewrite “users without orders” using:
   • LEFT JOIN
   • NOT EXISTS
   • NOT IN
   Then explain the NULL behavior of each approach.
9. Write a query using a subquery in FROM to calculate user totals, then join the derived table with users to display:

user name
total spent

10. Use EXPLAIN ANALYZE to compare:

IN

versus:

EXISTS

for finding users with orders.

Don’t worry if you can’t interpret the complete execution plan yet. Save the output – we’ll learn how to read it systematically.

Day 6 Preview

On Day 6, we’ll cover Indexes and EXPLAIN ANALYZE.

This is one of the most important transitions in the course because we’ll move from:

“Can I write the correct query?”

to:

“Can I explain why this query is fast or slow?”

We’ll cover:

• How PostgreSQL stores tables and indexes conceptually
• B-tree indexes
• Single-column indexes
• Composite indexes
• Index selectivity
• Sequential Scan
• Index Scan
• Bitmap Index Scan
• EXPLAIN
• EXPLAIN ANALYZE
• Why PostgreSQL sometimes ignores an index
• Indexes for foreign keys
• Rails migrations for indexes
• Query optimization interview questions

For a senior Rails interview, Day 6 is one of the highest-value lessons in the entire course.

Happy Learning! 🚀