Relational Databases — JOIN and Normalization

After completing this topic, you will be able to:

Explain why tables should be separated and write SQL to combine data using JOIN.

One Table is Not Enough

Let's say we're building an online shopping mall. What happens if we put all the order information into a single table?

orders (all information in one table):
+----+--------+-------+----------+--------+
| id | customer_name | email | product | price |
+----+--------+-------+----------+--------+
| 1 | Kim Hoon | h@ex | laptop | 1.5M |
| 2 | Kim Hoon | h@ex | mouse | 50K |
| 3 | Lee Soo | s@ex | keyboard | 100K |
+----+--------+-------+----------+--------+

Can you see the problems?

• Redundancy: "Kim Hoon" and "h@ex" are repeated every time they place an order.
• Risk of Modification: If Kim Hoon changes his email address, you have to update all rows. If you miss one, there will be inconsistencies.
• Risk of Deletion: If you delete all of Kim Hoon's orders, his customer information will be lost.

Table Separation — The Basics of Normalization

The solution is to separate related data into separate tables.

users table:                   orders table:
+----+--------+-------+         +----+---------+----------+--------+
| id | name | email |         | id | user_id | product | price |
+----+--------+-------+         +----+---------+----------+--------+
| 1 | Kim Hoon | h@ex |         | 1 | 1 | laptop | 1.5M |
| 2 | Lee Soo | s@ex |         | 2 | 1 | mouse | 50K |
+----+--------+-------+         | 3 | 2 | keyboard | 100K |
                                 +----+---------+----------+--------+

orders.user_id refers to users.id. This is a Foreign Key — the link that connects the two tables.

The process of removing redundancy and separating tables is called Normalization.

JOIN — Combining Separated Tables

Now that we've separated the tables, we need a way to combine them again. That's where JOIN comes in.

-- Retrieve the order list and attach the customer name
SELECT users.name, orders.product, orders.price
FROM orders
JOIN users ON orders.user_id = users.id;

Result:

+--------+----------+--------+
| name | product | price |
+--------+----------+--------+
| Kim Hoon | laptop | 1.5M |
| Kim Hoon | mouse | 50K |
| Lee Soo | keyboard | 100K |
+--------+----------+--------+

In JOIN ... ON, you specify which column to match on. orders.user_id = users.id — it combines rows where the order's user_id is the same as the user's id.

Types of JOIN

-- INNER JOIN: Only rows that match on both sides (default)
SELECT * FROM orders JOIN users ON orders.user_id = users.id;

-- LEFT JOIN: All rows from the left table, and matching rows from the right
-- Includes customers who have not placed an order
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Result: Customers with no orders will have product as NULL

In practice, INNER JOIN and LEFT JOIN are used most often.

Normalization — Why Separate?

The core principle of normalization is simple: Each fact should be stored in one place only.

Normalization is not "always good" — if tables are split too much, JOINs can become complex and performance can degrade. In practice, redundancy is sometimes intentionally allowed for performance reasons (denormalization). However, the foundation is normalization.