Writing JOINS in SQL

Week two at Flatiron School had us jumping straight into SQL. I was keen to learn more about SQL since I have experience running Wordpress sites, like the artist residency program I co-founded, Residency Unlimited.

I have spent unhealthy amounts of time googling SQL queries to figure out, for example, how to assign single posts from one post-type to another in phpMyAdmin (and back again, yuck!).

With a deeper grasp of databases and their management via SQL, I find it easier to grasp Object Orientated Programming and particularly value how Active Record streamlines the process between a database and your code.

Databases themselves are about as exhilarating as tanks of gas, but that’s ok, they don’t need to be exciting. Their purpose is to hold the fuel (in our case, content) that our car needs to function. SQL is a means to get the gas from tank to engine.

Lets dive into SQL JOINs by observing the following situation:

This diagram shows us how we can apply a JOIN to the above scenario.

A JOIN in SQL returns rows from multiple tables by identification through these three sections: INNER, LEFT, and RIGHT.

Let’s take a look at how different JOIN types do different things.

INNER JOIN

INNER JOIN — or simply JOIN, can be used to find a match between two tables. Both tables need to share a key. This key is used to make the match. Non-matching results are discarded.

SELECT \*
FROM TableD Dogs 
INNER JOIN TableC Car
ON Dogs.key = Car.key;

LEFT JOIN

LEFT JOIN — Returns all rows from the left table, and the matched rows from the right table.

SELECT \*
FROM TableD Dogs 
LEFT JOIN TableC Cars
ON Dogs.key = Cars.key;

You can also add a WHERE clause to return only rows from the outer section:

SELECT \*
FROM TableD Dogs 
LEFT JOIN TableC Cars
ON Dogs.key = Cars.key
WHERE Cars.key IS NULL;

RIGHT JOIN

RIGHT JOIN — The same as LEFT, but in reverse. Returns all rows from the right table, and the matched rows from the left table.

SELECT \*
FROM TableD d 
RIGHT JOIN TableC c
ON c.key = d.key;

And like in the previous example, return only rows from the outer section.

SELECT \*
FROM TableD Dogs 
RIGHT JOIN TableC Cars
ON Dogs.key = Cars.key
WHERE Dogs.key IS NULL;

FULL OUTER JOIN

FULL OUTER JOIN — Returns all rows when there is a match in ONE of the tables.

SELECT \*
FROM TableD Dogs 
FULL OUTER JOIN TableC Cars
ON Dogs.key = Cars.key;

Just want OUTER JOINS, excluding INNER?

SELECT \*
FROM TableD Dogs 
FULL OUTER JOIN TableC Cars
ON Dogs.key = Cars.key
WHERE Dogs.key IS NULL
OR Cars.key IS NULL;

Why is this important?

It reflects the DRY (Don’t Repeat Yourself) rule.

Only using INNER JOINs on every query returns a unique match discarding any non-matching entry. There is no way to know which dogs have never been in a car, or which car has never had a dog in it. You quickly find yourself going back and forth to find that specific information when you need it.

Using LEFT, RIGHT or FULL JOINs provides more detail in one fell swoop, resulting in faster insight to what your database contains and how its contents are related.

Other JOINS

There are other operators in SQL that help you parse rows. For example, using CROSS will join everything with everything, and UNION joins the results of two queries into one column and removes duplicate entries.

Check out SQL definitions on W3schools and try out your JOIN skills on Hack Array.