SQL JOINs combine related rows from two tables. They are fundamental for working with normalized databases, such as joining students to enrollments or customers to orders. The correct JOIN depends on whether you want only matches or also records that have no match on one or both sides.
Start with two related tables
Imagine a Students table with student_id and student_name, and an Enrollments table with student_id and course_code. The shared student_id connects rows in the two tables. A JOIN combines columns from related rows based on a condition.
Choose the join key carefully. Joining on a non-unique name can multiply rows or connect records incorrectly. Primary keys and foreign keys often provide a more reliable relationship.
Students(student_id, student_name)
Enrollments(student_id, course_code)INNER JOIN: return matching rows
INNER JOIN returns rows where the join condition matches on both sides. Students with no enrollment are excluded, as are enrollments without a matching student if referential integrity has not prevented them.
SELECT s.student_id, s.student_name, e.course_code
FROM Students AS s
INNER JOIN Enrollments AS e
ON s.student_id = e.student_id;LEFT JOIN: keep every row from the left table
LEFT JOIN returns every row from the left table and any matching rows from the right table. When no right-side row matches, the right-side columns contain NULL. This is useful when you want every student, including those who are not enrolled in a course.
SELECT s.student_id, s.student_name, e.course_code
FROM Students AS s
LEFT JOIN Enrollments AS e
ON s.student_id = e.student_id;RIGHT JOIN and FULL OUTER JOIN
RIGHT JOIN preserves every row from the right table and adds matching left-side data where available. Many queries can be rewritten as LEFT JOIN by swapping table order, which some teams find easier to read.
FULL OUTER JOIN preserves matches and unmatched rows from both tables. Support varies by database engine; some systems do not support FULL OUTER JOIN directly, so check the SQL dialect before using it.
-- RIGHT JOIN example
SELECT s.student_name, e.course_code
FROM Students AS s
RIGHT JOIN Enrollments AS e
ON s.student_id = e.student_id;Common JOIN mistakes
A join can return more rows than expected when the join key appears multiple times on either side. This is not automatically a database error: each matching combination may be part of the result. Understand whether the relationship is one-to-one, one-to-many or many-to-many.
Another common issue is filtering a LEFT JOIN in the WHERE clause on a right-table column. Requiring that column to have a value can remove unmatched rows and make the result behave like an inner join. Be explicit about whether a condition belongs in ON or WHERE.
- Check row counts before and after each join.
- Verify the uniqueness of keys and intended relationship cardinality.
- Qualify columns with table aliases.
- Understand how NULL values affect filters.
- Test a few records manually to confirm the output.
Understanding the concept is the first step.
Explore related resources or learn about subject-focused tutoring designed to help you understand the material.