SQL Practice Platform
Article •

Understanding Relationships in SQL - (With Examples & Practice)

When I first started learning SQL for interviews, I could write basic SELECT queries just fine. But the moment they asked something like "show all orders with customer names and product details," I got stuck.

The missing piece? Relationships between tables.

Today, I'm taking you through my journey of understanding SQL relationships — from confusion to confidence. We'll cover the three main types: One-to-One, One-to-Many, and Many-to-Many, with real examples using a simple e-commerce database.

By the end, you'll be able to design tables with proper relationships and write queries that combine data smoothly — exactly what companies like TCS, Infosys, and Deloitte expect in 2025 interviews.

All examples use the same database you can practice on live: sqlpractice.in

Let's dive in!

Why Do We Need Relationships?

Imagine building an app without relationships:

  • One giant table with repeated customer names in every order row → messy and wasteful
  • Hard to update (change one customer's email → edit hundreds of rows)
  • Slow queries and storage issues

Relationships let us split data into logical tables and connect them intelligently.

We use Primary Keys (PK) and Foreign Keys (FK) to create these connections.

The Three Types of Relationships

1. One-to-One (1:1)

One row in Table A connects to exactly one row in Table B (and vice versa).

Real Example:

  • users table (id, name, email)
  • user_profiles table (user_id, bio, profile_picture)

Each user has exactly one profile, and each profile belongs to one user.

Why separate? Privacy, optional data, or large fields (like images).

Practice Question 1 (Easy) Show user name and bio for users who have a profile. Practice this live →

2. One-to-Many (1:N) – The Most Common!

One row in Table A can connect to many rows in Table B, but each row in Table B connects to only one in Table A.

Classic Example:

  • One customer can place many orders
  • Each order belongs to only one customer

Practice Question 2 (Easy) List all customers and their order amounts (include customers with no orders). Practice Question 3 (Medium) Find customers who placed more than 3 orders. Practice these →

3. Many-to-Many (M:N)

One row in Table A can connect to many in Table B, and vice versa.

Real Example:

  • One product can be in many orders
  • One order can have many products

You can't directly link with just FK — you need a junction (bridge) table.

This is how Amazon's cart works!

Practice Question 4 (Medium) Show all orders with product names and quantities. Practice Question 5 (Hard) Find products that appear in more than 5 orders. Practice here →

 

Common Interview Questions on Relationships

  1. "Explain the difference between 1:1, 1:N, and M:N with examples."
  2. "How would you model students and courses?" (M:N → junction table)
  3. "Why use a junction table instead of arrays?"
  4. "What happens on DELETE CASCADE vs SET NULL?"

Practice Question 6–8 (Interview Level)

  • Design schema for blog posts and tags (M:N)
  • Users and their roles (consider both 1:1 and M:N approaches)
  • Employees and projects

Full relationship practice pack →

Key Takeaways

  • Use One-to-One for optional/separate data
  • One-to-Many is the most common (customer → orders)
  • Many-to-Many always needs a junction table
  • Foreign keys are your best friend — they make relationships work