INNER JOIN

#Introduction

This chapter starts the joins phase. Ranking SQL topics for a 5+ years Java backend interview:

1. Joins
2. Window functions
3. Indexes
4. Subqueries
5. Transactions

Almost every Spring Boot application uses joins, either directly or through JPA/Hibernate.

#Why Do We Need Joins?

Imagine an event booking system.

Who placed order 101? The orders table has only user_id = 1; the users table has 1 → Rahul. We need information from both tables — that's why we use a JOIN.

Think Like Java

Without a join, you make two database calls:

User user = userRepository.findById(1);
Order order = orderRepository.findById(101);

With a join, one SQL query returns Rahul, order 101, amount 500 — one database trip. Much faster.

#What is INNER JOIN?

With users Rahul (1), Amit (2), Neha (3) and orders 101 → user 1, 102 → user 2:

Neha isn't returned because she has no order.

#Primary Key and Foreign Key

users.user_id is the primary key; orders.user_id is a foreign key that references it.

#INNER JOIN Syntax

SELECT columns
FROM table1
INNER JOIN table2
    ON table1.column = table2.column;

Real Example

SELECT u.user_id,
       u.name,
       o.order_id,
       o.amount
FROM users u
INNER JOIN orders o
    ON u.user_id = o.user_id;

(JOIN on its own means INNER JOIN.)

Why Use Aliases?

Instead of users.user_id, write u.user_id — cleaner and easier to read. Almost every professional SQL query uses aliases.

#How MySQL Executes an INNER JOIN

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

Conceptually:

MySQL doesn't simply "merge" tables — it uses join algorithms (nested-loop joins, hash joins in MySQL 8.0.18+) and indexes when available. JOIN optimization has its own chapter.

#Multiple INNER JOINs

Real applications rarely join only two tables:

SELECT u.name,
       o.order_id,
       p.payment_status
FROM users u
INNER JOIN orders o
    ON u.user_id = o.user_id
INNER JOIN payments p
    ON o.order_id = p.order_id;

#JOIN vs Multiple Queries

❌ Bad approach: find user → find orders → find payment → find ticket → find venue — possibly many database calls.

✅ Better approach: one JOIN query. Benefits: one database call, less network latency, and less application-level overhead. JOINs are often preferable to repeatedly querying related data separately (the "N+1" pattern).

#INNER JOIN with WHERE

Orders placed by users from the IT department:

SELECT u.name,
       o.order_id
FROM users u
INNER JOIN orders o
    ON u.user_id = o.user_id
WHERE u.department = 'IT';

Logical Query Processing Order

#Spring Boot / JPA Example

@Entity
class User {

    @OneToMany(mappedBy = "user")
    private List<Order> orders;
}

When Hibernate needs users with their orders, it may generate SQL involving a join such as:

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

JPQL:

SELECT u
FROM User u
JOIN u.orders o

In JPQL, JOIN is an inner join by default. Understanding SQL joins helps you understand Hibernate-generated SQL.

#Performance Tips

Index the JOIN Columns

users.user_id is the primary key, so it's already indexed. orders.user_id is a foreign key and should be indexed for frequent joins (InnoDB creates an index for a foreign key automatically if none exists). Indexes can significantly improve join performance.

Join on Keys

Prefer primary key ↔ foreign key joins. Avoid joining on columns such as name, address or city unless the business requirement needs it — they may not be unique, may change over time, are larger than numeric IDs, and perform worse.

Select Only Needed Columns

SELECT *                     -- ❌

SELECT u.name, o.amount      -- ✅

Less data transferred, less memory, a cleaner result set and potentially better performance.

#Hands-On Assignment

Create the tables users (user_id, name), orders (order_id, user_id, amount) and payments (payment_id, order_id, payment_status), insert sample data, then write queries to:

1. Display user name with order ID.
2. Display user name with order amount.
3. Display order amount with payment status.
4. Display user name, order ID and payment status.
5. Display only orders where amount > 500.
6. Display orders for Rahul only.
7. Display all paid orders.

Challenge Problems

1. Join three tables.
2. Join four tables.
3. Display the highest-value order with the customer's name.
4. Display orders placed after a specific date.
5. Write a Spring Data JPA repository query that returns users with their orders.
6. Explain why indexing user_id improves JOIN performance.