Learn SQL: Day 3 – JOINs (One of the Most Important SQL Topic)

If I had to choose one SQL topic that appears most frequently in Senior Developer 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

Question:

I cannot see any difference in output between left join and full outer join with our example. can u create a new data to see me correct difference between them?

This is actually something that confuses many developers in interviews.

The reason you don’t see any difference is because our current data doesn’t have any “orphan” records in the orders table.

Let’s understand why.

Our Current Data

users

orders

Every order belongs to an existing user.

So there are:

• Users without orders (Bob, Alice)
• No orders without users

That’s why LEFT JOIN and FULL OUTER JOIN appear almost identical.

Let’s Create a Better Example

To see the difference, we need an order that doesn’t match any user.

However…

Our foreign key prevents that.

FOREIGN KEY (user_id)
REFERENCES users(id)

This is a good thing because it maintains data integrity.

So for learning purposes, we’ll create another table without a foreign key.

Step 1

DROP TABLE IF EXISTS orders_demo;
CREATE TABLE orders_demo (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    amount NUMERIC(10,2)
);

Notice:

❌ No foreign key.

Step 2

Insert data

INSERT INTO orders_demo(user_id, amount)
VALUES
(1,100),
(1,200),
(2,300),
(999,400);

Now we have:

users

orders_demo

Notice:

user_id = 999

There is no matching user.

This is our orphan order.

INNER JOIN

SELECT
    u.id,
    u.name,
    o.amount
FROM users u
INNER JOIN orders_demo o
ON u.id = o.user_id;

Result

The orphan order disappears.

LEFT JOIN

SELECT
    u.id,
    u.name,
    o.amount
FROM users u
LEFT JOIN orders_demo o
ON u.id = o.user_id;

Result

Question:

Where is the orphan order?

It is gone!

Why?

Because LEFT JOIN keeps every row from the left table (users). Since there is no user with id = 999, there is nothing on the left to preserve.

FULL OUTER JOIN

SELECT
    u.id,
    u.name,
    o.user_id,
    o.amount
FROM users u
FULL OUTER JOIN orders_demo o
ON u.id = o.user_id;

Result

Now you finally see the difference!

The last row exists only because of FULL OUTER JOIN.

When to use FULL OUTER JOIN?

Check the page: https://railsdrop.com/learn-sql-day-3-when-to-use-full-outer-join/

Quick Quiz

Given these tables:

A

B

Without running SQL, what rows do you expect from:

1. INNER JOIN
2. LEFT JOIN (A LEFT JOIN B)
3. RIGHT JOIN (A RIGHT JOIN B)
4. FULL OUTER JOIN

Try answering these on paper first. If you can do that confidently, you’ve truly understood how the different joins work.

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! 

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