SQL JOINS

A JOIN is used to combine rows from two or more tables based on a related column between them. Instead of storing all information in one giant table, databases split data into separate tables and connect them using keys. JOINs are the mechanism that brings this related data back together in a single query result.

Think of it like two lists: one list of students and another list of their enrolled courses. A JOIN links a student to their course by matching the student ID that appears in both lists.

The Reference Tables

All JOIN examples use these two tables:

Students Table

Enrollments Table

Notice that StudentID 4 (Sneha) has no enrollment, and enrollment 105 references StudentID 5 which does not exist in the Students table. These edge cases show how different JOINs behave differently.

Types of SQL JOINs

INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables. Rows that do not have a match on either side are excluded from the result.

Syntax

SELECT table1.column, table2.column
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

Example

SELECT s.StudentName, s.City, e.CourseName, e.Grade
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID;

Result:

Sneha Kapoor (StudentID 4) is excluded because she has no enrollment. The History enrollment (StudentID 5) is also excluded because there is no student with StudentID 5 in the Students table.

LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table, and the matching rows from the right table. If there is no match, the right table columns show NULL.

Syntax

SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

Example

SELECT s.StudentName, s.City, e.CourseName, e.Grade
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID;

Result:

Sneha Kapoor now appears in the result even though she has no enrollment. Her CourseName and Grade are shown as NULL because no matching row exists in the Enrollments table.

A common use of LEFT JOIN: finding students who have not enrolled in any course:

SELECT s.StudentName
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
WHERE e.StudentID IS NULL;

Result: Sneha Kapoor — she is the only student with no enrollment record.

RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN returns all rows from the right table, and the matching rows from the left table. If there is no match, the left table columns show NULL.

Example

SELECT s.StudentName, e.CourseName, e.Grade
FROM Students s
RIGHT JOIN Enrollments e ON s.StudentID = e.StudentID;

Result:

The History course (StudentID 5) now appears with NULL in the StudentName column because StudentID 5 doesn't exist in the Students table. All enrollment records are included.

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables. Where there is a match, both sides are shown. Where there is no match, NULL fills in the missing side.

SELECT s.StudentName, e.CourseName
FROM Students s
FULL OUTER JOIN Enrollments e ON s.StudentID = e.StudentID;

MySQL does not support FULL OUTER JOIN directly. It can be simulated using a UNION of a LEFT JOIN and a RIGHT JOIN:

SELECT s.StudentName, e.CourseName
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID

UNION

SELECT s.StudentName, e.CourseName
FROM Students s
RIGHT JOIN Enrollments e ON s.StudentID = e.StudentID;

JOIN Summary Table

Self JOIN

A Self JOIN joins a table to itself. This is useful for comparing rows within the same table — for example, finding students from the same city.

SELECT A.StudentName AS Student1, B.StudentName AS Student2, A.City
FROM Students A
INNER JOIN Students B ON A.City = B.City AND A.StudentID != B.StudentID;

This finds pairs of students who live in the same city.

Key Points to Remember

• Always specify the join condition using ON — joining on a shared key column.
• INNER JOIN is the most common and returns only matching rows.
• LEFT JOIN is used when all rows from the primary (left) table must appear in the result.
• Table aliases (like s and e) make JOIN queries shorter and easier to read.
• A single query can have multiple JOIN clauses — each joining a new table.

Summary

SQL JOINs are the primary mechanism for combining data from multiple related tables. INNER JOIN returns only matched rows, LEFT JOIN keeps all rows from the left table, RIGHT JOIN keeps all from the right, and FULL OUTER JOIN keeps everything from both. Mastering JOINs is one of the most important skills in SQL, as nearly every real-world database query involves more than one table.