my rules for simple JOINs

Joins in SQL let you take 2 tables and combine them into one.

On the left side of the page, there is an illustration of two small tables with the words “INNER JOIN” between them. One has columns labelled a, b, c, and d, the other has columns labelled x, y, and z. On the right side of the page, there is a big table with columns a, b, c, d, x, y, and z.

Joins can get really complicated, so we’ll start with the simplest way to join. Here are the rules I use for 90% of my joins:

Rule I: only use LEFT JOIN and INNER JOIN

There are other kinds of joins (RIGHT JOIN, CROSS JOIN, FULL OUTER JOIN), but of the time I only use LEFT JOIN and INNER JOIN.

Rule 2: refer to columns as table_name.column_name

You can leave out the table name if there’s just one column with that name, but it can get confusing.

Rule 3: Only include 1 condition in your join

Here’s the syntax for a LEFT JOIN:

table1 LEFT JOIN table2 ON <any boolean condition>

I usually stick to a very simple condition, like this:

table1 LEFT JOIN table2
   ON table1.some_column = table2.other_column

Rule 4: One of the joined columns should have unique values

If neither of the columns is unique, you’ll get strange results like this:

owners_bad:

INNER JOIN

cats_bad:

(these are “bad” versions of the “owners” and “cats” tables that don’t JOIN well)

owners_bad INNER JOIN cats_bad
   ON owners_bad.name = cats_bad.owner