When a Single Table Isn't Enough: Why We Need Joins (JOIN)?

In previous chapters, we learned how to retrieve data from a single table (e.g., the users table). This is as simple as opening a worksheet in Excel and using filters to find data.

But in the real business world, data is extremely complex.
Imagine a large e-commerce platform (like PChome or Shopee)—its data would never be crammed into a single table. Why?
If you put "member names, emails, product names, product prices, purchase quantities, and shipping addresses" all in one table, when the same member makes 100 purchases, their name and email would be duplicated 100 times. This not only wastes hard drive space but also means if a member changes their name, you'd have to update 100 records at once, which is highly error-prone!

Thus, relational databases invented their greatest design: splitting data into different tables and connecting them with "keys (Key)."

• Users table (users): Stores basic member information. Each member has a unique ID (id).
• Products table (products): Stores product catalogs. Each product has a unique ID (id).
• Orders table (orders): This is the core! The orders table does not store member names or product names. It only stores user_id and product_id.

When you need to generate a complete report like "who bought what," you must tell the database to "stitch" these three tables together. This action is called JOIN in SQL.

---

Four Common JOIN Patterns (No Need to Memorize—AI Will Help)

Although in the Vibe Coding era we don’t need to memorize syntax, understanding JOIN concepts helps you craft more precise prompts for AI.

1. INNER JOIN (Intersection): Most common. Only retrieves records where both tables have matching data. "Find members who have made purchases."
2. LEFT JOIN (Left Outer Join): Prioritizes the left table. Shows all data from the left table, even if there’s no matching data on the right. "List all members, showing their orders if they’ve made purchases, or leaving order fields blank if they haven’t."
3. RIGHT JOIN (Right Outer Join): The opposite of LEFT JOIN, prioritizing the right table.
4. FULL OUTER JOIN (Union): Retrieves all data from both tables, filling unmatched fields with NULL.

Vibe Prompt: Let AI Write God-Level JOIN Syntax for You

Just describe your table relationships in plain language, and AI will instantly generate the JOIN syntax for you.

【Advanced Join Report Prompt】
I have three tables:

1. users (id, name, email)
2. products (id, title, price)
3. orders (id, user_id, product_id, quantity, created_at)

Please write SQL to list "all order details."
The report must show: order ID, buyer’s name (name), product name (title), quantity (quantity), and the order’s total amount (calculate as price * quantity, named total_amount).
Finally, sort by time from newest to oldest.

AI’s Perfect SQL Output:

SELECT 
    o.id AS order_id,
    u.name AS user_name,
    p.title AS product_name,
    o.quantity,
    (p.price * o.quantity) AS total_amount,
    o.created_at
FROM 
    orders o
INNER JOIN 
    users u ON o.user_id = u.id
INNER JOIN 
    products p ON o.product_id = p.id
ORDER BY 
    o.created_at DESC;

(See! AI automatically used INNER JOIN to link the three tables and even wrote the formula to calculate the total amount!)

---

Group Calculations (GROUP BY) and Pivot Analysis

Beyond joining tables, the most common task in data analysis is "statistics and grouping." This is like Excel’s "Pivot Table."

For example, your boss walks up to your desk and says, "Calculate how many units of each product were sold last month, the total revenue for each, and which product was the most profitable?"

If you manually create a pivot table in Excel, you might go cross-eyed. But in SQL, this is called GROUP BY (Grouping) combined with Aggregate Functions (e.g., SUM, COUNT, AVG).

Vibe Prompt: Instantly Generate Business Decision Reports

【Product Revenue Analysis Prompt】
Using the orders and products tables from earlier, write SQL to:
Calculate "total units sold" and "total revenue generated" for each product.
Sort by total revenue in descending order to instantly identify the most profitable product.

AI’s God-Level Report SQL:

SELECT 
    p.title AS product_name,
    SUM(o.quantity) AS total_sold_quantity,
    SUM(p.price * o.quantity) AS total_revenue
FROM 
    products p
LEFT JOIN 
    orders o ON p.id = o.product_id
GROUP BY 
    p.id, p.title
ORDER BY 
    total_revenue DESC NULLS LAST;

When you execute this in Supabase’s SQL Editor, you’ll get a crystal-clear report like:

1. Ergonomic AI Chair | Sold 5 units | Total revenue 60,000
2. Vibe Premium Mechanical Keyboard | Sold 12 units | Total revenue 42,000

With the power of Vibe Coding and SQL, you’ll never drown in tens of thousands of records again. You just need to ask the right questions, and AI will deliver clear answers! In the next chapter, we’ll tackle an even harder challenge: how to use databases to protect your users’ passwords!