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 understand 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) passed into the engine, so that our car can move. SQL is a means to get the gas from tank to engine.

Lets dive into 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.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade