What’s missing? Find out using OUTER JOIN’s in MySQL with examples.

What’s missing? What matches in one table but not another? MySQL (and SQL in general) have a way to find out. More than likely, when working with a normalized database, you are faced with these types of questions. Querying multiple tables with JOIN‘s on matched values, only goes so far to answering this type of question. We will see how to leverage an OUTER JOIN, for these requests.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and Database:

• Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
• MySQL 5.7.22

Photo by Todd Diemer on Unsplash

Here are a couple of questions OUTER JOIN‘s can answer for us:

• Determine which rows, if any, from one table have no match in another table.
• Determine missing values with a test for NULL. (Example provided below)

I’ll use two tables that form a mock pipe tracking unit, representing assets with and without degrees that form a ‘bend’.

The structure of the tables is setup based on the has_degree column in table pipe.
The tinyint(1) data type represents MySQL’s version of a boolean value. Anywhere 1 is present this represents ‘truth’, meaning that particular record has a matching row in table degree_value. Hence that row (or pipe asset) has a degree_amount and is a ‘bend’.

Both rows are ‘joined’ and matched on the pipe_id column present in both tables.
Table degree_value provides the number (in degrees) by way of the degree_amount column for that matching row.
I’ll query for data present in both tables, then look at how we can leverage an OUTER JOIN on this data set.

Suppose we are tasked with determining what rows in the pipe table, do not have a matching row in table degree_value?
Well, we could always query with an INNER JOIN (see this blog post for INNER JOIN examples in-depth) and then manually track the pipe_id‘s from table pipe that are not a part of the query results.
Here is that specific INNER JOIN:

But, there is a better way to do this.
And a prime use case for an OUTER JOIN.

The above INNER JOIN query returns rows that do match.
But we want those rows that do not match.
Those which are not a ‘bend’.

A OUTER JOIN returns rows from table’s involved where there is a match for rows on the specified value, in addition to rows where there is no match.
MySQL provides both a LEFT and RIGHT variant.

In truth, the OUTER keyword is optional as shown with the next 2 queries:

Identical results with or without the optional OUTER keyword.

* Note: Moving forward, I will omit the OUTER keyword where appropriate.

Replacing ON with a USING clause is also optional syntax:

The first table named in the LEFT JOIN, pipe, is considered the ‘left’ table and table degree_value is considered the ‘right’.
Through the above queries, wherever the degree_amount column value is NULL, that represents no match on the ‘right’ table (degree_value).

So in this example query, NULL indicates that row (the pipe entity) does not have a degree_amount (no match).

Here is an explanation provided from the official documentation (at the time of this writing) in section 13.2.9.2 Join Syntax.

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all
columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no
counterpart in another table

Matter of fact, we can restrict the results set leveraging a WHERE clause, returning only rows with NULL for column dv.degree_amount.

Let’s see:

Wait a minute.
There are rows that should match that condition.
Ah ha.
Silly me.
I need to wrap NULL in quotes.

No query results.
And a warning.
Let’s look at that too while we are at it.

What’s going on here?

* Handy Tidbit:
I’ll share something with you that plagued me for some time.
NULL is a special value that is not equivalent to anything.
Not 0 (zero).
Not an empty string (e.g., ' ').
Not a space (e.g.,  ).
Not even NULL itself.

How then do you test a column for NULL?
You still test for truth, but with the IS keyword.

Now we are getting somewhere.
These are explicit results that can be further processed, if needed, to answer the question of what pipe_id‘s do not have a degree_amount, hence are not a ‘bend’ entity in this context.

Earlier in the post, I alluded to the differences between the LEFT and RIGHTtables.

So let’s ‘flop’ the original query, naming degree_value as the RIGHT table and see what those query results are:

That results set looks familiar, doesn’t it?
Same query results from the above-demonstrated INNER JOIN.
What about naming pipe for the RIGHT JOIN table?

That yields identically to the original LEFT JOIN query with the same implications for the NULL value in the degree_amount column.

Recommended Reading

There is a wealth of detail and information in the official documentation from the following sections I highly recommend consulting:

• 8.2.1.7 Nested Join Optimization
• 8.2.1.8 Left Join and Right Join Optimization.
• 13.2.9.2 JOIN Syntax

I find LEFT JOIN‘s highly effective (and interesting) when exploring unfamiliar data sets.
Plying one to monitor for any NULL‘s on the ‘right’ table provides insight of what is missing or incomplete.
Try them out for yourself to discover how complete or incomplete your data set is.
I would love to know of those use cases where they have enhanced answering your toughest questions, exposed other notable metrics, or surprised you during initial data discovery/exploration phase.
Feel free to leave any comments.
Explore the official MySQL 5.7 Online Manual for more information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.