Joins in SQL Explained.

What are SQL Joins and Why do we need it ?

In relational databases, data is intentionally split across multiple tables to avoid duplication and maintain consistency. This concept is called normalization.

Let's understand normalization to understand why do we need joins.

Suppose a single table storing orders:

Orders Table

order_id	customer_name	customer_email	product_name	product_price
1	Saurav	saurav@mail.com	Laptop	70000
2	Saurav	saurav@mail.com	Mouse	500
3	Rahul	rahul@mail.com	Laptop	70000

Did you see any problem here ?

If not then god might save your company working you are in ;)

1. Data Duplication: "Saurav's name and email" & "Laptop and its price" are repeated multiple times.

2. Update Anomaly: If Saurav changes email → you must update every row Miss one → inconsistent data

3. Insert Anomaly: Want to add a new customer without an order? → Not possible without inserting fake order data

4. Delete Anomaly: If you delete all Saurav’s orders → you lose customer info entirely

This is the problem normalization solves by separating the data to avoid this anomalies.

Now lets back to our main topic,

We have successfully splited the data but now the question is how we can retrieve the related data from different tables ?

This is where SQL Joins come in.

What is a JOIN?

A JOIN is used to combine rows from two or more tables based on a relationship between them, typically defined using keys (like primary key and foreign key).

Instead of storing all data in one place, we store it in separate tables and use joins to reconstruct the complete information when needed.

Types of Joins

Let’s understand joins using a simple example.

Imagine you are building a movie booking system.

You have two tables:

1. users

id | name
---------
1  | Saurav
2  | Amit
3  | Neha

2. bookings

id | user_id | movie
----------------------
1  |    1    | Dhurandhar
2  |    1    | Hera Pheri
3  |    2    | Dhamaal

Now suppose you want to answer:

“Which user booked which movie?”

This requires combining both tables.

1. INNER JOIN

What it does

Returns only the data where there is a match in both tables.

Query

SELECT u.name, b.movie
FROM users u
INNER JOIN bookings b ON u.id = b.user_id;

Result

Saurav | Dhurandhar
Saurav | Hera Pheri
Amit   | Dhamaal

Explanation

• Neha is missing because she has no booking.

• Only matching rows are returned.

Analogy

Think of two lists:

• People who signed up

• People who bought tickets

INNER JOIN shows only people who did both.

---

2. LEFT JOIN

What it does

Returns all rows from the left table, even if there is no match in the right table.

Query

SELECT u.name, b.movie
FROM users u
LEFT JOIN bookings b ON u.id = b.user_id;

Result

Saurav | Dhurandhar
Saurav | Hera Pheri
Amit   | Dhamaal
Neha   | NULL

Explanation

• Neha appears, even though she didn’t book anything.

• Her movie is NULL because no match exists.

Analogy

You have a list of all users. You try to attach booking info if it exists. If not, you still keep the user.

---

3. RIGHT JOIN

What it does

Returns all rows from the right table, even if there is no match in the left table.

Query

SELECT u.name, b.movie
FROM users u
RIGHT JOIN bookings b ON u.id = b.user_id;

Result

Saurav | Dhurandhar
Saurav | Hera Pheri
Amit   | Dhamaal

Explanation

• All bookings are shown.

• If a booking had no user (rare case), user would be NULL.

Analogy

You care about all bookings. Users are attached if available.

4. FULL JOIN

What it does

Returns all rows from both tables, whether they match or not.

Query

SELECT u.name, b.movie
FROM users u
FULL JOIN bookings b ON u.id = b.user_id;

Result

Saurav | Dhurandhar
Saurav | Heri Pheri
Amit   | Dhamaal
Neha   | NULL

(If there were bookings without users, those would also appear with NULL user.)

Analogy

Combine both lists completely:

• All users

• All bookings

Even if they don’t match.

5. CROSS JOIN

What it does

Returns every possible combination of rows from both tables.

Query

SELECT u.name, b.movie
FROM users u
CROSS JOIN bookings b;

Result

Saurav | Dhurandhar
Saurav | Hera Pheri
Saurav | Dhamaal
Amit   | Dhurandhar
Amit   | Hera Pheri
Amit   | Dhamaal
Neha   | Dhurandhar
Neha   | Hera Pheri
Neha   | Dhamaal

Explanation

• Every user is paired with every booking.

Analogy

Everyone is matched with every possible option.

Summary

Join Type	What it returns
INNER JOIN	Only matching rows
LEFT JOIN	All left + matching right
RIGHT JOIN	All right + matching left
FULL JOIN	Everything from both sides
CROSS JOIN	All combinations

Final Thought

Joins are not just about syntax. They reflect how you think about relationships:

• Do you want only matches? → INNER JOIN

• Do you want everything from one side? → LEFT JOIN

• Do you want everything from both? → FULL JOIN

Understanding this clearly will help you write correct queries and design better systems.