If you were like me, I was constantly confused by LEFT / RIGHT / INNER / OUTER JOINs in SQL.

The Internet even has this hilarious meme:

A front end developer eats alone because he doesn’t know how to join tables

I found the best visual from StackOverflow:

My Notes

To help my tiny working memory, I like to summarize things in FAQ style notes.

Note: Below are short explanations only. If you want concrete examples, StackOverflow has plenty.

Q: JOIN or INNER JOIN?

They are the same thing. JOIN is shorthand for INNER JOIN.

Q: LEFT JOIN or LEFT OUTER JOIN?

They are the same thing. LEFT JOIN is shorthand for LEFT OUTER JOIN.

Q: What is OUTER JOIN?

When you do LEFT, if there is no match on the right, you pad right table columns with null.

Q: For LEFT JOIN, what if there are multiple matches on the right table?

It leads to duplicates. If one left row matches two right rows, it returns two rows.

Therefore, LEFT JOIN results should be equal to or greater than the number of rows of the left table.

Q: What is FULL OUTER JOIN?

Combine of LEFT and RIGHT OUTER JOIN. Meaning, if there is no match on the other side, those columns are padded with NULL values.

In practice, we rarely use this.

Q: When to use LEFT JOIN?

Right table may or may not have matches. Regardless, you want all rows on the left to be present in the result set.

Q: What is LEFT JOIN (if NULL)?

You add a NULL check in WHERE condition after LEFT JOIN. Something like:

select * from a
left join b on a.key = b.key
where b.key is null

Q: What about CROSS JOIN and others? Why not include them?

In practice, they are rarely used. If you are interested, here is a good explanation.

Closing Thoughts

SQL JOIN is something that continued to puzzle me until I took notes. Since then, whenever I have questions, I recall the Venn diagram, or even go though my notes again. After a few iterations, joining table almost becomes a second nature to me.

With practice, you can do it, too.

If you want to learn more, sign up for my SQL course at BackToSQL.com.

Happy JOIN-ing!