SQL

🧩 Day 7 — SQL JOINS

Photo of Mathimaa Mathimaa3 weeks ago
0 0 4 minutes read

🧩 Day 7 — SQL JOINS

🔹 What is a Join?

A Join is used to combine rows from two or more tables based on a related column between them.

🔸 Types of Joins

Join Type Description Result
1. INNER JOIN Returns only the matching records from both tables. Common rows only
2. LEFT OUTER JOIN Returns all records from left table, and matched rows from right table. All left + matched right
3. RIGHT OUTER JOIN Returns all records from right table, and matched rows from left table. All right + matched left
4. FULL OUTER JOIN Returns all matched and unmatched rows from both tables. All data from both
5. CROSS JOIN Returns Cartesian product (every row of table A × every row of table B). m × n combinations
6. SELF JOIN Joins a table with itself. Useful for hierarchical data (e.g., employee–manager). Same table joined on itself

🔹 Example Tables

employees

employee_id first_name manager_id department_id
101 John 201 10
102 David 201 20

departments

department_id department_name
10 Sales
20 IT
30 HR

🔹 Examples

1️⃣ Inner Join

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

✅ Returns matched rows from both tables.

2️⃣ Left Join

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

✅ Returns all employees, even if they don’t belong to a department.

3️⃣ Right Join

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

✅ Returns all departments, even if no employees belong to them.

4️⃣ Full Outer Join

SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

✅ Returns all records from both tables (matched + unmatched).

5️⃣ Cross Join

SELECT e.employee_id, d.department_name
FROM employees e
CROSS JOIN departments d;

✅ Produces a Cartesian product (if 10 employees × 5 departments → 50 rows).

6️⃣ Self Join

SELECT e.employee_id, e.first_name AS employee_name,
m.employee_id AS manager_id, m.first_name AS manager_name
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id;

✅ Joins employees table with itself to show who reports to whom.

🧮 SQL Joins Explained with SET Examples

Each SET represents two tables (A and B, or C and D, etc.) — we’ll calculate what each type of join returns.

⚙️ Join Logic Reminder

Join Type Description What You Get
INNER JOIN Only rows with matching values in both tables Common values
LEFT JOIN All rows from left table + matched rows from right All left + matches
RIGHT JOIN All rows from right table + matched rows from left All right + matches
FULL JOIN All rows from both tables (matched + unmatched) Everything
CROSS JOIN Every row in A combined with every row in B Cartesian product (A×B)

🔸 SET 1

Table A

ID
1
2
3
4
5
6

Table B

ID
1
2
3
4
5
8

Results

Join Type Result IDs Count
INNER JOIN 1, 2, 3, 4, 5 5
LEFT JOIN 1, 2, 3, 4, 5, 6 6
RIGHT JOIN 1, 2, 3, 4, 5, 8 6
FULL JOIN 1, 2, 3, 4, 5, 6, 8 7
CROSS JOIN 6×6 = 36 combinations 36

🔸 SET 2

Table C

ID
1
2
2
3
3
4
5

Table D

ID
1
2
3
5
8

Results

Join Type Result IDs Count
INNER JOIN 1, 2, 2, 3, 3, 5 6
LEFT JOIN 1, 2, 2, 3, 3, 4, 5 7
RIGHT JOIN 1, 2, 2, 3, 3, 4, 5, 8 8
FULL JOIN 1, 2, 2, 3, 3, 4, 5, 8 8
CROSS JOIN 7×5 = 35 combinations 35

🔸 SET 3

Table E

ID
1
2
2
2
2
2

Table F

ID
2
2
3
4

Results

Join Type Result IDs Count
INNER JOIN 10 rows (because 5 twos in E × 2 twos in F = 10) 10
LEFT JOIN All 6 rows from E + 5 matches (total 11) 11
RIGHT JOIN 2s (10) + unmatched 3,4 → total 12 12
FULL JOIN 1 (from E unmatched) + 2×10 + 3 + 4 = 13 13
CROSS JOIN 6×4 = 24 24

🔸 SET 4

Table G

ID
2
2
2
2
2

Table H

ID
2
2
2

Results

Join Type Result IDs Count
INNER JOIN 5×3 = 15 (all are 2) 15
LEFT JOIN same as inner (all match) 15
RIGHT JOIN same as inner (all match) 15
FULL JOIN same as inner (all match) 15
CROSS JOIN 5×3 = 15 combinations 15

🔸 SET 5

Table I

ID
0
1
2
2
3
4
NULL

Table J

ID
1
2
2
4
5
0
NULL

Results

Join Type Result IDs Count
INNER JOIN 0, 1, 2, 2, 2, 2, 4 (NULLs don’t match) 7
LEFT JOIN all from I (7) + matched right (2 extra) → 9 9
RIGHT JOIN all from J (7) + matched left (2 extra) → 9 9
FULL JOIN all from both (including nulls & unmatched 3,5) → 11 11
CROSS JOIN 7×7 = 49 combinations 49

✅ KEY POINTS TO REMEMBER

  • INNER JOIN ignores unmatched rows.

  • LEFT JOIN keeps all left rows.

  • RIGHT JOIN keeps all right rows.

  • FULL JOIN keeps everything.

  • CROSS JOIN multiplies all rows.

  • NULL values never match in join conditions (e.g., NULL = NULL is false).

Photo of Mathimaa Mathimaa3 weeks ago
0 0 4 minutes read

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button