Learn SQL: Day 5 – Subqueries, EXISTS, IN, NOT EXISTS, ANY, and ALL

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:

User	Orders
John	100, 250, 75
Mary	500, 300
Bob	200
Alice	800, 150
David	No orders
Sara	1000, 1200

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:

Number of orders:

Average:

457.5

Now the outer query becomes conceptually:

			
SELECT *
FROM orders
WHERE amount > 457.5;

Result:

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:

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:

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:

Average:

141.67

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

Mary:

			
500
300

Average:

Above average:

Alice:

			
800
150

Average:

Above average:

Sara:

			
1000
1200

Average:

Above average:

Bob has only one order:

Average:

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:

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:

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:

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:

What does the innermost query calculate?
Is the innermost query correlated?
What does the middle EXISTS query check?
Which users will be returned?
Will a user with exactly one order be returned?
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.

Find users who have at least one completed order.
Find users who have no completed orders.
Find orders greater than the global average order amount.
Find orders greater than the average order amount for their respective user.
Find users whose total order amount is greater than the average total spending across all users who have orders.
Find users who have an order greater than every order placed by John. Use ALL.
Find users who have an order greater than at least one order placed by Sara. Use ANY.
Rewrite “users without orders” using:
- LEFT JOIN
- NOT EXISTS
- NOT IN
Then explain the NULL behavior of each approach.
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

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! 🚀

Author: Abhilash

Hi, I’m Abhilash! A seasoned web developer with 15 years of experience specializing in Ruby and Ruby on Rails. Since 2010, I’ve built scalable, robust web applications and worked with frameworks like Angular, Sinatra, Laravel, Node.js, Vue and React. Passionate about clean, maintainable code and continuous learning, I share insights, tutorials, and experiences here. Let’s explore the ever-evolving world of web development together! View all posts by Abhilash

Today’s Goals

Part 1 – Prepare the Practice Data

Part 2 – What Is a Subquery?

Rails Equivalent

Part 3 – Scalar Subqueries

What Happens if the Subquery Returns Multiple Rows?

Part 4 – IN with a Subquery

Rails Equivalent

Using select

Using pluck

Senior-Level Insight

Part 5 – NOT IN

Part 6 – The NOT IN + NULL Trap

Part 7 – EXISTS

How Does EXISTS Work?

Correlated Subquery

Why SELECT 1?

Part 8 – NOT EXISTS

Compare With LEFT JOIN

Rails Equivalent: where.missing

Rails Equivalent: where.associated

Part 9 – IN vs EXISTS

IN

EXISTS

Which Is Faster?

Part 10 – Correlated Subqueries

Rails Equivalent

Part 11 – Subqueries in FROM

Part 12 – ANY

Part 13 – ALL

Important ANY / ALL Mental Model

Part 14 – ANY with ActiveRecord Arrays

Part 15 – JOIN vs IN vs EXISTS

JOIN

IN

EXISTS

How Should You Choose?

Part 16 – Practical PostgreSQL Exercises

Exercise 1

Exercise 2

Exercise 3

Exercise 4

Exercise 5

Exercise 6

Part 17 – Common Mistakes

Mistake 1: Using = with a Multi-Row Subquery

Mistake 2: Using NOT IN Without Considering NULL

Mistake 3: Using pluck When You Want a SQL Subquery

Mistake 4: Using JOIN + DISTINCT for Every Existence Check

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

Part 18 – Senior Interview Questions

Q1

Q2

Q3

Q4

Q5

Q6

Q7

Q8

Q9

Q10

Part 19 – Today’s Interview Challenge

Homework

Day 6 Preview

Share this:

Related

Author: Abhilash

Leave a comment Cancel reply

Part 4 – `IN` with a Subquery

Using `select`

Using `pluck`

Part 5 – `NOT IN`

Part 6 – The `NOT IN` + `NULL` Trap

Part 7 – `EXISTS`

How Does `EXISTS` Work?

Why `SELECT 1`?

Part 8 – `NOT EXISTS`

Rails Equivalent: `where.missing`

Rails Equivalent: `where.associated`

Part 9 – `IN` vs `EXISTS`

`IN`

`EXISTS`

Part 11 – Subqueries in `FROM`

Part 12 – `ANY`

Part 13 – `ALL`

Important `ANY` / `ALL` Mental Model

Part 14 – `ANY` with ActiveRecord Arrays

Part 15 – `JOIN` vs `IN` vs `EXISTS`

Mistake 1: Using `=` with a Multi-Row Subquery

Mistake 2: Using `NOT IN` Without Considering `NULL`

Mistake 3: Using `pluck` When You Want a SQL Subquery

Mistake 4: Using `JOIN + DISTINCT` for Every Existence Check