Rahul connects his tables with JOINs

INNER JOIN, LEFT JOIN, aliases — combining two tables in one query

⏱ 13 min 4 commands 5 takeaways

🔗

In this chapter

Rahul

Backend developer, connecting two tables

The story

Rahul had users and orders in two separate tables. He needed to connect them. His senior said: you need JOINs.

Rahul stared at JOIN examples for an hour feeling confused. His senior said: stop reading examples. Let me explain it with a real story.

THE PROBLEM WITHOUT JOIN

CREATE TABLE orders (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT NOT NULL,
    product    VARCHAR(200),
    amount     DECIMAL(10,2),
    status     ENUM('pending','paid','cancelled') DEFAULT 'pending',
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO orders (user_id, product, amount, status) VALUES
    (1, 'MacBook Air',  85000.00, 'paid'),
    (1, 'iPhone 15',    79999.00, 'paid'),
    (2, 'iPad Pro',     95000.00, 'pending'),
    (3, 'AirPods Pro',  24999.00, 'paid');

Without JOIN you would need to query orders, then look up each user_id separately. With 10,000 orders that means 10,000 separate queries. JOIN combines both tables in one query.

INNER JOIN -- MATCH IN BOTH TABLES

SELECT u.name, u.email, o.product, o.amount, o.status
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

Read as: "from orders, join with users, connecting orders.user_id to users.id"

INNER JOIN only shows rows that match in BOTH tables. A user with no orders does not appear. An order with an invalid user_id does not appear.

LEFT JOIN -- INCLUDE EVERYONE

LEFT JOIN returns all rows from the left table even with no match on the right. No match shows NULL.

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

Users with no orders appear with NULL for product and amount.

FIND USERS WITH NO ORDERS

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

After a LEFT JOIN, WHERE right_table.id IS NULL finds rows with no match -- users who never ordered.

REAL BUSINESS QUERIES

-- Total spent per customer:
SELECT u.name, COUNT(o.id) AS orders, SUM(o.amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.id, u.name
ORDER BY total_spent DESC;

-- Top 5 customers by revenue:
SELECT u.name, SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.id, u.name
ORDER BY revenue DESC
LIMIT 5;

-- Paid orders this month with customer name:
SELECT u.name, o.product, o.amount, o.ordered_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
AND o.ordered_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
ORDER BY o.ordered_at DESC;

Rahul's senior said: JOINs are not complicated. They are just asking -- what connects these two tables, and what do I want from both?

Key takeaways

INNER JOIN returns only rows that have a match in BOTH tables — unmatched rows are excluded entirely

LEFT JOIN returns ALL rows from the left table even when no match exists on the right — unmatched shows NULL

The ON clause (ON u.id = o.user_id) is the bridge between tables — it tells MySQL how they connect

Table aliases (FROM users u) make multi-table queries readable — always alias when joining

WHERE right_table.id IS NULL after a LEFT JOIN finds rows with no matching records — perfect for finding gaps

Commands from this chapter

$ SELECT u.name, o.product, o.amount FROM users u JOIN orders o ON u.id = o.user_id;

Basic INNER JOIN

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

Users with no orders

$ SELECT u.name, COUNT(o.id) AS cnt, SUM(o.amount) AS total FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name ORDER BY total DESC;

Total spent per customer

$ SELECT u.name, o.product FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'paid' ORDER BY o.amount DESC LIMIT 10;

Top 10 paid orders with customer name