Introduction

The difference between SQL join types is really about which unmatched rows you want to keep. INNER JOIN keeps only matches, LEFT JOIN keeps all rows from the left table, RIGHT JOIN keeps all rows from the right table, and FULL JOIN keeps unmatched rows from both sides.

A Small Example Dataset

Assume these two tables:

employees

1id  name   department_id
21   Ada    10
32   Linus  20
43   Sam    null

departments

department_id  department_name
10             Engineering
30             Finance

Notice two important facts:

• employee Ada has a matching department
• employee Linus points to a department that does not exist in the second table
• department Finance has no matching employee

Those missing matches are what make the join types different.

INNER JOIN: Keep Only Matches

1SELECT e.name, d.department_name
2FROM employees e
3INNER JOIN departments d
4    ON e.department_id = d.department_id;

Result idea:

• 'Ada appears'
• 'Linus does not appear'
• 'Sam does not appear'
• 'Finance does not appear'

INNER JOIN answers the question, "show me rows where both sides matched the join condition."

LEFT JOIN: Keep All Rows from the Left Table

1SELECT e.name, d.department_name
2FROM employees e
3LEFT JOIN departments d
4    ON e.department_id = d.department_id;

Now every employee row stays in the result:

• 'Ada appears with Engineering'
• 'Linus appears with NULL department data'
• 'Sam appears with NULL department data'

This is the join to use when the left table is your primary subject and the right table is optional lookup data.

RIGHT JOIN: Keep All Rows from the Right Table

1SELECT e.name, d.department_name
2FROM employees e
3RIGHT JOIN departments d
4    ON e.department_id = d.department_id;

Now every department row stays in the result:

• 'Engineering appears with Ada'
• 'Finance appears with NULL employee data'

RIGHT JOIN is valid, but many teams prefer rewriting it as a LEFT JOIN with the table order swapped because it is easier to read consistently.

FULL JOIN: Keep Unmatched Rows from Both Sides

1SELECT e.name, d.department_name
2FROM employees e
3FULL OUTER JOIN departments d
4    ON e.department_id = d.department_id;

This keeps:

• matched rows such as Ada and Engineering
• unmatched employees such as Linus and Sam
• unmatched departments such as Finance

So you can think of FULL OUTER JOIN as the union of the unmatched-row behavior of left and right joins.

The Join Condition Still Matters

A common source of confusion is not the join type but where filtering happens. If you use a LEFT JOIN and then put a filter on the right table in the WHERE clause, you can accidentally turn it back into inner-join behavior.

Example:

1SELECT e.name, d.department_name
2FROM employees e
3LEFT JOIN departments d
4    ON e.department_id = d.department_id
5WHERE d.department_name = 'Engineering';

That removes rows where d.department_name is NULL, which means unmatched left-side rows are gone. If you need to preserve left-side rows, push the condition into the join or handle NULL explicitly.

Common Pitfalls

• Memorizing join names without understanding which unmatched rows survive.
• Using RIGHT JOIN when a clearer LEFT JOIN with swapped tables would read better.
• Filtering the right table in WHERE after a LEFT JOIN and accidentally removing unmatched rows.
• Assuming FULL OUTER JOIN exists in every database engine.
• Forgetting that NULL join keys do not match ordinary equality conditions.

Summary

• 'INNER JOIN keeps only rows that match on both sides.'
• 'LEFT JOIN keeps all rows from the left table and fills unmatched right-side columns with NULL.'
• 'RIGHT JOIN does the same thing in the other direction.'
• 'FULL OUTER JOIN keeps unmatched rows from both tables.'
• The best join type depends on which table's unmatched rows you want to preserve.