SQL JOINS --- Core Concept¶

A JOIN combines rows from multiple tables using related columns.
we can use Where in joins it Filters results after joins and give us final result rows.
Join tables first Then filter rows where amount > 500

Sample Tables¶

customers¶

customer_id	customer_name	city
1	Rahul	Mumbai
2	Amit	Delhi
3	Sneha	Pune

orders¶

order_id	customer_id	product_id	amount
101	1	201	500
102	1	202	1000
103	2	201	700
104	4	203	900

products¶

product_id	product_name	category
201	Laptop	Electronics
202	Phone	Electronics
203	Chair	Furniture

INNER JOIN¶

Concept¶

Returns only matching rows from both tables.

Example

SELECT customers.customer_name,
       orders.order_id,
       orders.amount
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Result¶

customer_name	order_id	amount
Rahul	101	500
Rahul	102	1000
Amit	103	700

Important¶

Order 104 disappears because customer_id 4 does not exist in customers.

That is exactly what INNER JOIN does.

LEFT JOIN¶

Concept¶

Returns:

all rows from left table
matching rows from right table

If no match exists → NULL values.

Example¶

SELECT customers.customer_name,
       orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Result¶

customer_name	order_id
Rahul	101
Rahul	102
Amit	103
Sneha	NULL

RIGHT JOIN¶

Concept¶

Returns:

all rows from right table
matching rows from left table

Example¶

SELECT customers.customer_name,
       orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

Result¶

customer_name	order_id
Rahul	101
Rahul	102
Amit	103
NULL	104

FULL OUTER JOIN¶

Concept¶

Returns all matching + non-matching rows from both tables.

Problem¶

MySQL does NOT support FULL OUTER JOIN directly.

Alternative in MySQL¶

SELECT customers.customer_name,
       orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id

UNION

SELECT customers.customer_name,
       orders.order_id
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

CROSS JOIN¶

Concept¶

Creates every possible combination.

Example¶

SELECT customers.customer_name,
       products.product_name
FROM customers
CROSS JOIN products;

Result Count¶

If:

3 customers
3 products

Result = 3 × 3 = 9 rows

SELF JOIN¶

Concept¶

SELF JOIN is not an actual SQL keyword. Join a table to itself using INNER, LEFT, RIGHT JOIN

Useful for:

employee-manager hierarchy
parent-child relationships

employees Table¶

emp_id	emp_name	manager_id
1	Rahul	NULL
2	Amit	1
3	Sneha	1

Example¶

SELECT e.emp_name AS employee,
       m.emp_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;

JOIN on 2 Columns¶

Concept¶

Sometimes one column is not enough.

You join using multiple conditions.

Example Tables¶

student_marks¶

student_id	subject_id	marks
1	101	90
1	102	85
2	101	70

subject_details¶

student_id	subject_id	teacher
1	101	Sharma
1	102	Mehta
2	101	Roy

INNER JOIN on 2 Columns¶

SELECT student_marks.student_id,
       student_marks.subject_id,
       student_marks.marks,
       subject_details.teacher
FROM student_marks
INNER JOIN subject_details
ON student_marks.student_id = subject_details.student_id
AND student_marks.subject_id = subject_details.subject_id;

Why Multi-Column JOIN Matters¶

Why join on 2 columns?¶

Because:

student_id alone is not unique
subject_id alone is not unique

The combination:

(student_id, subject_id)

uniquely identifies each subject record for a student.

JOIN on 3 Columns¶

Example Tables¶

sales¶

store_id	product_id	sale_date	quantity
1	201	2025-01-01	5
1	202	2025-01-01	3

inventory¶

store_id	product_id	stock_date	stock
1	201	2025-01-01	100
1	202	2025-01-01	200

JOIN Using 3 Columns¶

SELECT sales.store_id,
       sales.product_id,
       sales.sale_date,
       sales.quantity,
       inventory.stock
FROM sales
INNER JOIN inventory
ON sales.store_id = inventory.store_id
AND sales.product_id = inventory.product_id
AND sales.sale_date = inventory.stock_date;

Table Aliases¶

Concept¶

Aliases shorten table names.

Without aliases, complex joins become unreadable garbage.

Example¶

SELECT c.customer_name,
       o.order_id
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;

JOIN + WHERE¶

where can be applied any table within join Example

SELECT c.customer_name,
       o.amount
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.amount > 600;

JOIN + GROUP BY¶

Example¶

SELECT c.customer_name,
       SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

JOIN + HAVING¶

SELECT c.customer_name,
       SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_name
HAVING SUM(o.amount) > 1000;

Multiple Table JOIN (3 Tables)¶

Example¶

SELECT c.customer_name,
       o.order_id,
       p.product_name
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id
INNER JOIN products p
ON o.product_id = p.product_id;

Visual Understanding¶

customers
    |
customer_id
    |
orders
    |
product_id
    |
products

This is relational thinking.

Without understanding relationships visually, SQL joins become memorized nonsense.

Common JOIN Mistakes¶

1. Missing JOIN Condition¶

SELECT *
FROM customers, orders;

This creates accidental CROSS JOINs.

Potentially millions of useless rows.

Real-World JOIN Pattern¶

E-commerce Example¶

SELECT customers.customer_name,
       products.product_name,
       orders.amount
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id
INNER JOIN products
ON orders.product_id = products.product_id;

This pattern appears everywhere:

Amazon
banking
HR systems
hospital databases
analytics dashboards

Execution Flow of JOIN Query¶

SQL logically processes:

FROM
JOIN
ON
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

If this order is fuzzy in your head, complex queries will keep confusing you.

Practice Problems¶

Easy¶

Find all customers with their orders.

Medium¶

Find total spending by each customer.

Hard¶

Find products never ordered by customers.

Hint:

LEFT JOIN + WHERE NULL