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 — 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 — 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 — 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 — 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;
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.
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.