Skip to main content

Basic Joins

  • Joins allow you to retrieve data from multiple tables in a relational database by linking them through related columns via primary keys and foreign keys.

  • Joins are essential because data in relational databases is stored across many normalized tables rather than in one large table. Understanding joins lets you combine this data into meaningful results.

INNER JOIN​

  • Returns only the rows where both tables have matching values.

  • Use when you want data that exists in both tables.

  • Returns students only if they have an enrollment record.

SELECT students.name, enrollments.course_id
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id;

LEFT JOIN (Left Outer Join)​

  • Returns all rows from the left table, and matching rows from the right table. If no match exists, the right side shows NULL.

  • Use when you want everything from the left table, even without matches.

  • Returns all students, including those not enrolled in any course.

SELECT students.name, enrollments.course_id
FROM students
LEFT JOIN enrollments ON students.id = enrollments.student_id;

RIGHT JOIN (Right Outer Join)​

  • Returns all rows from the right table, and matching rows from the left table.

  • Use when you want everything from the right table, even without matches.

  • Returns all enrollments, even if some don't have a matching student.

SELECT students.name, enrollments.course_id
FROM students
RIGHT JOIN enrollments ON students.id = enrollments.student_id;

FULL JOIN (Full Outer Join)​

  • Returns all rows from both tables, with matches where possible. Unmatched rows on either side return NULL.

  • Some databases like MySQL require workarounds since FULL JOIN is not supported natively.

  • Returns students with enrollments, students without enrollments, and enrollment records without matching students.

SELECT students.name, enrollments.course_id
FROM students
FULL JOIN enrollments ON students.id = enrollments.student_id;

JOIN Summary Table​

Join TypeReturns
INNER JOINOnly matching rows from both tables
LEFT JOINAll left rows + matching right rows (NULL if no match)
RIGHT JOINAll right rows + matching left rows (NULL if no match)
FULL JOINAll rows from both tables, matched or not