Day: February 14, 2024

If I had to choose one SQL topic that appears most frequently in Senior Rails interviews, it would be:

JOINs

Most Rails developers know:

User.joins(:orders)

But many cannot explain:

• What SQL Rails generates
• How PostgreSQL executes it
• Why duplicates occur
• When to use joins
• When to use includes
• When JOINs become slow

A senior engineer should be comfortable with all of these.

Today’s Goals

By the end of Day 3, you’ll understand:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• CROSS JOIN
• Self JOIN
• How Rails translates associations into JOINs
• N+1 query problem
• joins vs includes
• Interview questions

Step 1: Create Fresh Tables

Let’s create a simple system.

Users

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100)
);

Orders

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  amount NUMERIC(10,2),
  CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id)
  REFERENCES users(id)
);

Insert Sample Data

Users:

INSERT INTO users(name)
VALUES
('John'),
('Mary'),
('Bob'),
('Alice');

Orders:

INSERT INTO orders(user_id, amount)
VALUES
(1,100),
(1,200),
(2,300),
(2,400),
(2,500);

Current data:

users

orders

Notice:

Bob has no orders
Alice has no orders

This becomes important.

What is a JOIN?

A JOIN combines rows from multiple tables.

Think:

users
+
orders
=
business information

The database uses a common column:

users.id
=
orders.user_id

1. INNER JOIN

Most common JOIN.

Returns only matching rows.

Query

SELECT
  users.id,
  users.name,
  orders.amount
FROM users
INNER JOIN orders
  ON users.id = orders.user_id;

Result:

Notice:

Bob disappeared
Alice disappeared

Why?

Because they have no matching order.

Visual

users         orders
John      <->   100
John      <->   200
Mary      <->   300
Mary      <->   400
Mary      <->   500
Bob       X
Alice     X

Only matches survive.

Rails Equivalent

User.joins(:orders)

Generated SQL:

SELECT users.*
FROM users
INNER JOIN orders
ON orders.user_id = users.id;

Interview Question

What type of JOIN does Rails joins use?

Answer:

INNER JOIN

Many candidates miss this.

2. LEFT JOIN

Returns:

All rows from LEFT table
+
matching rows from RIGHT table

Query

SELECT
  users.name,
  orders.amount
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

Result:

Notice:

Bob exists
Alice exists

Even without orders.

Visual

LEFT TABLE = users
Keep everything

John  -> order
Mary  -> order
Bob   -> NULL
Alice -> NULL

Rails Equivalent

User.left_joins(:orders)

Generated SQL:

LEFT OUTER JOIN

Practical Example

Find users without orders.

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

Result:

Bob
Alice

Rails:

User.left_joins(:orders)
    .where(orders: { id: nil })

Common Interview Question

Find customers who never placed an order.

Expected answer:

LEFT JOIN
+
IS NULL

3. RIGHT JOIN

Opposite of LEFT JOIN.

Keep all rows from right table.

SELECT *
FROM users
RIGHT JOIN orders
ON users.id = orders.user_id;

In real-world Rails projects:

Rarely used

Most engineers rewrite it as LEFT JOIN.

4. FULL OUTER JOIN

Keep everything.

SELECT *
FROM users
FULL OUTER JOIN orders
ON users.id = orders.user_id;

Returns:

All users
+
All orders

matched where possible.

Used occasionally for:

• reporting
• analytics
• reconciliation

Rare in Rails applications.

5. CROSS JOIN

Creates every possible combination.

Example:

CREATE TABLE colors (
  color VARCHAR(20)
);
INSERT INTO colors
VALUES ('Red'),('Blue');

Sizes:

CREATE TABLE sizes (
  size VARCHAR(20)
);
INSERT INTO sizes
VALUES ('S'),('M');

Query:

SELECT *
FROM colors
CROSS JOIN sizes;

Result:

Red   S
Red   M
Blue  S
Blue  M

Every row paired with every row.

Formula:

RowsA × RowsB

Interview Question:

10 rows × 100 rows

How many rows?

Answer:

1000

6. Self JOIN

A table joins itself.

Very common interview topic.

Create employees:

CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100),
  manager_id BIGINT
);

Insert:

INSERT INTO employees
(name, manager_id)
VALUES
('CEO', NULL),
('Manager1',1),
('Manager2',1),
('Developer1',2),
('Developer2',2);

Query:

SELECT
  e.name AS employee,
  m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

Result:

Rails

class Employee < ApplicationRecord
  belongs_to :manager,
             class_name: "Employee",
             optional: true
  has_many :subordinates,
           class_name: "Employee",
           foreign_key: :manager_id
end

Why Duplicates Occur

Look at:

SELECT *
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

Mary has:

3 orders

Therefore:

Mary appears 3 times

JOINs multiply rows.

This is one of the most misunderstood SQL concepts.

DISTINCT After JOIN

Sometimes we want unique users.

SELECT DISTINCT users.*
FROM users
JOIN orders
ON users.id = orders.user_id;

Rails

User.joins(:orders).distinct

The N+1 Query Problem

Every Rails interview asks this.

Suppose:

users = User.all
users.each do |user|
  puts user.orders.count
end

Queries:

SELECT * FROM users;

Then:

SELECT * FROM orders WHERE user_id=1;
SELECT * FROM orders WHERE user_id=2;
SELECT * FROM orders WHERE user_id=3;
...

100 users:

101 queries

Called:

N+1 problem

Fix Using includes

User.includes(:orders)

Rails loads:

SELECT * FROM users;

and

SELECT * FROM orders
WHERE user_id IN (...);

Only 2 queries.

joins vs includes

This is a favorite interview question.

joins

Used for filtering.

User.joins(:orders)

SQL:

INNER JOIN

Purpose:

Filter data

includes

Used for eager loading.

User.includes(:orders)

Purpose:

Avoid N+1

Example

Find users with orders.

User.joins(:orders)

Display users and orders.

User.includes(:orders)

Interview Question

Which is better?

joins

or

includes

Answer:

Depends on the problem.

Different purposes.

Real Interview Queries

Find users with orders

SELECT DISTINCT users.*
FROM users
JOIN orders
ON users.id = orders.user_id;

Rails:

User.joins(:orders).distinct

Find users without orders

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

Rails:

User.left_joins(:orders)
    .where(orders: { id: nil })

Find total orders per user

SELECT
  users.name,
  COUNT(orders.id)
FROM users
LEFT JOIN orders
ON users.id = orders.user_id
GROUP BY users.name;

We’ll study GROUP BY in Day 4.

Senior-Level Insights

1. Most Rails JOINs are INNER JOINs

joins

means:

INNER JOIN

2. LEFT JOIN + IS NULL

is the standard pattern for:

Find missing records

Examples:

• users without orders
• customers without subscriptions
• products without inventory

3. JOINs Multiply Rows

One user:

3 orders

becomes:

3 result rows

Always remember this.

4. Understand the Generated SQL

When writing:

User.joins(:orders)

you should mentally see:

INNER JOIN orders
ON orders.user_id = users.id

immediately.

Practical Exercises

Exercise 1

Find all users with orders.

Exercise 2

Find all users without orders.

Exercise 3

Add 3 more orders for John.

Run:

INNER JOIN

Observe duplicates.

Exercise 4

Use:

DISTINCT

to remove duplicates.

Exercise 5

Create:

categories
products

and practice:

INNER JOIN
LEFT JOIN

Homework

Create the following schema:

authors
books
publishers

Relationships:

Author has many Books
Publisher has many Books
Book belongs to Author
Book belongs to Publisher

Insert sample data.

Write queries:

1. Books with author names
2. Books with publisher names
3. Authors without books
4. Publishers without books
5. Unique authors who have books

Write both:

• SQL
• ActiveRecord

Day 4 Preview

Tomorrow we’ll cover one of the highest-frequency SQL interview topics:

GROUP BY & Aggregations

Including:

• COUNT
• SUM
• AVG
• MIN
• MAX
• GROUP BY
• HAVING
• Aggregate queries in Rails
• Real interview problems such as:
  • Top customers
  • Revenue calculations
  • Most purchased products
  • Reporting queries

This is where SQL starts becoming analytical rather than just relational.

Happy Learning!