SQL Tutorial
•
•
4 min read
Relationships in SQL – One-to-One, One-to-Many, Many-to-Many Explained
Relationships in SQL – One-to-One, One-to-Many, Many-to-Many Explained
How to Think About It (From Noob Brute Force to Proper Approach)
Hey, I’m Gaurav.
When I first saw questions like “Show all users with their orders” or “Find products that have been ordered by multiple customers”, I had no idea how to approach them.
My brute-force brain would do this:
Noob Way (What most beginners do):
- Try to put everything in one single table
- Repeat customer name, email, product name in every row
- Write very long and messy queries
- Get confused when data changes (e.g., customer changes email → have to update 50 rows)
This approach feels easy at first but becomes a nightmare later.
Today I’ll show you exactly how I learned to think about relationships in SQL — from that confused brute-force stage to a clean, professional way.
We’ll use the same simple database you can practice on: [sqlpractice.in](https://sqlpractice.in)
Step 1: Why Do We Even Need Relationships?
Noob thinking:
“Why can’t I just keep everything in one table? It’s simpler.”
Better thinking process:
- Ask yourself: “What data is repeating again and again?”
- If the same customer name and email is repeating in every order row → that’s a red flag.
- Repeating data = storage waste, update problems, and bugs.
Realisation:
We split data into separate tables so that each piece of information is stored only once.
Step 2: The Three Types of Relationships (How to Think About Them)
Instead of memorizing definitions, ask yourself these two simple questions every time:
1. Can one record in Table A be connected to many records in Table B?
2. Can one record in Table B be connected to many records in Table A?
Based on the answer, you get one of three types.
1. One-to-One (1:1)
Noob way:
I put all user details in one table (name, email, bio, profile picture).
Better thinking:
- Bio and profile picture are optional and can be very large.
- Not every user will fill them.
- So I should separate them.
Correct approach:
- `users` table → id, name, email
- `user_profiles` table → user_id (FK), bio, profile_picture
Practice Question 1 (Easy)
Show user name and bio for users who have a profile.
[Try it live →](https://sqlpractice.in/ )
2. One-to-Many (1:N) – Most Common
Noob way:
Put customer name and email inside every order row.
Better thinking:
- One customer can place many orders.
- But each order belongs to only one customer.
- So customer details should be stored only once.
Correct approach:
- `users` table (id, name, email)
- `orders` table (id, user_id → foreign key to users.id, amount, ordered_at)
Practice Question 2 (Easy)
Show customer name and all their order amounts.
[Try it live →](https://sqlpractice.in/ )
Practice Question 3 (Medium)
Find customers who have placed more than 3 orders.
[Try it live →](https://sqlpractice.in/ )
3. Many-to-Many (M:N)
Noob way:
I try to put multiple product_ids in one column inside orders table (bad idea).
Better thinking:
- One order can have many products.
- One product can be in many orders.
- Neither table can hold the other’s ID directly.
Correct approach:
Create a junction table (bridge table) called `order_items`:
```sql
CREATE TABLE order_items (
order_id BIGINT REFERENCES orders(id),
product_id BIGINT REFERENCES products(id),
quantity INT,
PRIMARY KEY (order_id, product_id)
);
```
Practice Question 4 (Medium)
Show all orders with product names and quantities.
[Try it live →](https://sqlpractice.in/ )
Practice Question 5 (Hard)
Find products that have been ordered by more than 5 different customers.
How to Approach Any Relationship Question (The Thinking Framework I Use)
1. Identify the two entities (User & Order, Product & Order, etc.)
2. Ask: Can one entity have many of the other?
3. Decide the type (1:1, 1:N, or M:N)
4. If M:N → create junction table
5. Always add foreign key constraints with proper `ON DELETE` behaviour
6. Think about real-world operations: What happens when a user is deleted? (CASCADE vs SET NULL)
Final Thoughts
I used to think relationships were just “some technical thing”.
Now I see them as the foundation of clean database design.
The moment you start thinking in terms of “one-to-many” or “many-to-many”, your queries become much cleaner and your database much more maintainable.
Go practice all these relationship questions on [sqlpractice.in](https://sqlpractice.in) — the live editor will show you instantly whether your thinking is correct.
Next article (Day 2): Keys in SQL – Primary, Foreign, Unique & Composite
See you tomorrow!