Combining Data from Multiple Tables
In a well-designed database, data is spread across multiple tables to avoid redundancy. JOINs allow you to combine rows from two or more tables based on related columns.
Types of JOINs
Students Enrollments
┌────┬─────────┐ ┌────┬─────────┬──────┐
│ ID │ Name │ │SID │CourseID │Grade │
├────┼─────────┤ ├────┼─────────┼──────┤
│ 1 │ Alice │ │ 1 │ 101 │ A │
│ 2 │ Bob │ │ 2 │ 101 │ B │
│ 3 │ Charlie │ │ 3 │ 102 │ A │
│ 4 │ Diana │ └────┴─────────┴──────┘
└────┴─────────┘
INNER JOIN — Only rows that match in both tables:
SELECT Students.Name, Enrollments.CourseID, Enrollments.Grade
FROM Students
INNER JOIN Enrollments ON Students.ID = Enrollments.SID;
Result:
┌─────────┬─────────┬──────┐
│ Name │CourseID │Grade │
├─────────┼─────────┼──────┤
│ Alice │ 101 │ A │
│ Bob │ 101 │ B │
│ Charlie │ 102 │ A │
└─────────┴─────────┴──────┘
(Diana excluded — no enrollment)
LEFT JOIN — All rows from the left table, matching rows from right (NULL if no match):
RIGHT JOIN — All rows from the right table, matching rows from left.
FULL OUTER JOIN — All rows from both tables (NULL where no match).
Self Join
-- Find employees and their managers
SELECT E.Name AS Employee, M.Name AS Manager
FROM Employees E
LEFT JOIN Employees M ON E.ManagerID = M.ID;