If you've felt distant or estranged from SQL so far in the series, never fear: we're about to discover the magic of what makes relational databases so... relational. Turn down the lights and put on your favorite Marvin Gaye track; we're about to connect on another level.

I find that existing attempts to explain Database relations (JOINs in particular) have been an utter failure in illustrating these concepts. The Venn Diagrams we're all accustomed to seeing mean nothing to somebody who has never seen a JOIN occur, and even then, do they really describe what's happening? I'd love to toss together some quick animations as an alternative, but I'll probably settle for something mediocre like the rest of us.

Relational Databases in Action

As much as we've covered SQL, we still haven't had "the talk." Oh God no, not that talk; I meant the obligatory example-of-how-two-tables-might-relate-to-one-another talk. This talk is a bit less awkward, but it won't prepare you for the finer things in life. Just kidding, data is the finer part of life. Or at least it is in mine. Let's not linger on that too long.

Let's look at the most common scenario used to illustrate data relationships: the customers vs. orders predicament. Let's say we decided to open up an Organic Vegan Paleo Keto Kale Voltron 5000 health-food marketplace to cater to a high-end clientele: pretentious rich assholes. It just so happens that the "rich asshole" market is very receptive to best practices in customer relations, so we start a CRM to track our best customers. This record-keeping helps us pretend to remember the names and personalities of our clientele:

Customers Table

id first_name last_name email gender state phone
653466635 Timothea Crat tcrat0@bandcamp.com Female Washington 206-220-3752
418540868 Kettie Fuggle kfuggle1@cafepress.com Female California 661-793-1372
857532654 Boonie Sommerland bsommerland2@soundcloud.com Male North Carolina 919-299-0715
563295938-4 Red Seldon rseldon3@addthis.com Male Indiana 765-880-7420
024844147 Marika Gallatly mgallatly4@loc.gov Female New York 718-126-1462
900992907 Sharlene McMaster smcmaster5@gmpg.org Female Nevada 775-376-0931
329211747-X Grover Okey gokey6@weather.com Male Texas 915-913-0625
656608031 Farly Pluck fpluck7@buzzfeed.com Male Texas 432-670-8809
906380018 Sumner Pickerell spickerellb@bloglovin.com Male Colorado 719-239-5042

On the other hand, we need to keep track of inventory and items sold. Since we're already swiping credit cards and getting all this personal customer data, why not associate purchases with loyal customers? Thus, we have a list of transactions which looks something as such:

Orders Table

item_id customer_id item_purchased first_name last_name amount date_purchased
82565290-530d-4272-9c8b-38dc0bc7426a 653466635 Creme De Menthe Green Timothea Crat $8.57 5/13/18
9cfa5f5c-6a9c-4400-8f0f-f8262a787cd0 653466635 Veal Inside - Provimi Timothea Crat $5.77 3/3/18
5dea0cce-c6be-4f35-91f6-0c6a1a8b8f11 656608031 Arizona - Plum Green Tea Grover Okey $1.72 9/6/18
b4813421-12e8-479b-a3b6-3d1c4c539625 656608031 Beer - Fruli Grover Okey $4.05 10/1/18
4e7c8548-340f-4e89-a7f1-95173dcc6e53 656608031 Boogies Grover Okey $1.97 12/17/18
65261e94-494d-48cc-8d5a-642ae6921600 656608031 Cup - 3.5oz; Foam Grover Okey $1.84 11/28/18
1bfdca0f-d54a-4845-bbf5-982813ab4a65 656608031 Arizona - Green Tea Grover Gauford $0.22 5/23/18
d20d7add-bad4-4559-8896-d4f6d05aa3dd 906380018 Lemonade - Strawberry; 591 Ml Sumner Tortoishell $7.98 10/11/18
12134510-bc6c-4bd7-b733-b549a61edaa3 906380018 Pasta - Cappellini; Dry Sumner Wash $0.31 11/13/18
80f1957c-df4d-40dc-b9c4-2c3939dd0865 906380018 Remy Red Berry Infusion Sumner Pisculli $1.25 12/31/18
a75f7593-3312-43e4-a604-43405f02efdd 906380018 Veal - Slab Bacon Sumner Janaszewski $9.80 3/9/18
c6ef1f55-f35d-4618-8de7-36f59ea6653a 906380018-5 Beans - Black Bean; Dry Sumner Piegrome $1.36 12/11/18
c5b87ee3-da94-41b1-973a-ef544a3ffb6f 906380018 Calypso - Strawberry Lemonade Sumner Piegrome $7.71 2/21/19
e383c58b-d8da-40ac-afd6-7ee629dc95c6 656608031 Basil - Primerba; Paste Mohammed Reed $2.77 10/21/18
d88ccd5b-0acb-4144-aceb-c4b4b46d3b17 656608031 Cheese - Fontina Mohammed Reed $4.24 7/14/18
659df773-719c-447e-a1a9-4577dc9c6885 656608031 Cotton Wet Mop 16 Oz Jock Skittles $8.44 1/24/19
ff52e91e-4a49-4a52-b9a5-ddc0b9316429 656608031 Pastry - Trippleberry Muffin - Mini Jock Skittles $9.77 11/17/18
86f8ad6a-c04c-4714-8f39-01c28dcbb3cb 656608031 Bread - Olive Jock Skittles $4.51 1/10/19
e7a66b71-86ff-4700-ac57-71291e6997b0 656608031 Wine - White; Riesling; Semi - Dry Farly Pluck $4.23 4/15/18
c448db87-1246-494a-bae4-dceb8ee8a7ae 656608031 Melon - Honey Dew Farly Pluck $1.00 9/10/18
725c171a-452d-45ef-9f23-73ef20109b90 656608031 Sugar - Invert Farly Pluck $9.04 3/24/18
849f9140-1469-4e23-a1de-83533af5fb88 656608031 Yokaline Farly Pluck $3.21 12/31/18
2ea79a6b-bfec-4a08-9457-04128f3b37a9 656608031 Cake - Bande Of Fruit Farly Pluck $1.57 5/20/18

Naturally, customers buy more than one item; they buy a lot. Especially that Farly Pluck guy at the bottom- quite the unfortunate auto-generated name.

As standalone tables, the customers and orders tables each serve at least one straightforward purpose. The customers table helps us with consumer demographic analysis, whereas the orders table ensures we’re making money and not getting robbed. While important, neither of the functions is particularly revolutionary: this basic level of record-keeping has been at the core of nearly every business since the 70s.

The ability to combine data enables us to gain far more significant insights. We can reward loyal customers, cater to the needs of individuals based on their preferences, and perhaps even sell the personal data of where and when Mr. Pluck has been every Tuesday and Thursday for the past 4 months to the highest bidding data broker (hint: he's at our store).

Thanks to relational databases, we are neither limited to single monolithic tables nor are we shackled by the constraints of the tables we set up front. Associating data is trivial, as long as we have a means by which to associate it by. Below is a visualization of matching a foreign key in our orders table to a primary key in our Customers table:

An Order's Foreign Key References a customer's ID

The above illustrates what we've already brushed on: Foreign Key association. Primary and foreign keys are essential to describing relations between the tables, and in performing SQL JOINs. Without further adieu, let's join some data.

Joining Sets of Data

To “join” multiple data sets is consolidating multiple tables into one.

The manner of this consolidation is determined by which of the four methods of joining tables we use: INNER JOIN, RIGHT JOIN, LEFT JOIN, and OUTER JOIN (LEFT and RIGHT JOINs are kind of the same, but whatever). Regardless of the type of JOIN, all joins have the following in common:

  • Row comparison: we look for rows where the values of a column in Table A match the values of a column in Table B.
  • Consolidation of columns: The purpose of any join is to come away with a table containing columns from both tables.

LEFT & RIGHT JOINsomes

LEFT and RIGHT joins cover a myriad of use cases. With some creativity, left/right joins can help solve problems we may not have expected. The terms "left" and "right" refer to the table we'd like to join on when reading from left to right. When joining tables via LEFT JOIN, the first table in our query will be the "left" table. Alternatively, a RIGHT JOIN refers to the last table.

When we say "table to join on," we specify which table's key values will be the "authority" for our merge. In a LEFT MERGE, all of the records in Table A will survive the merge:

  • For rows that have a match in Table B, these rows will be 'extended' to include the data in Table B. This means the new columns added to Table A from Table B will contain data for all rows where an association has been made.
  • For rows that exist in Table A but do NOT have a match in Table B, these rows are unaffected: they will contain the same data as before the join, with values in the new columns left blank.
  • Keys that exist in Table B but do NOT exist in Table A will be discarded. The purpose of these joins is to enrich the data of the primary table.

Below is an example of an actual left join I use to power the Kanban board module on our "Projects" page. The left table is a table of JIRA issues, and the right table is a collection of issue-based customizations, such as custom icons and colors for issue types. Take a look at how this data is associated and what makes it into the final table:

Illustrating how SQL `LEFT JOIN`s work
Keys on the LEFT table determines which rows stay or go

The structure of a LEFT JOIN query looks as such:

SELECT 
  table_1.*, table_2.*
FROM
  t1
    LEFT JOIN
  t2 ON t1.column_name = t2.column_name;

Perform a LEFT JOIN on a SELECT query & and table

Here's an example with actual values:

SELECT
	first_name,
	last_name,
	order_date,
	order_amount
FROM
	customers c
	LEFT JOIN orders o ON c.customer_id = o.customer_id;

Example of a LEFT JOIN with actual values

Compare this to a RIGHT JOIN:

SELECT
	first_name,
	last_name,
	order_date,
	order_amount
FROM
	customers c
	RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Syntax of a RIGHT JOIN, with the same data as above

INNER JOIN (or CROSS JOIN)

Inner joins are the most conservative method for joining sets of data. Unlike LEFT or RIGHT joins, there is no authoritative table in an INNER JOIN: only rows that contain a match in all tables will survive the join. All other rows will be ignored:

SELECT
	table_1.column_name (s),
	table_2.column_name (s),
FROM
	table1
	INNER JOIN table2 ON table1.column_name = table2.column_name;

Performing an INNER JOIN between two tables

Because inner joins will only act on rows that match all affected tables, an INNER JOIN will typically contain the most "complete" data set (highest number of columns satisfied with values), but will contain the fewest number of rows.

OUTER JOINs

Outer joins come in a few different flavors. Generally speaking, outer joins maximize the data that will survive after the join is performed.

LEFT (OR RIGHT) OUTER JOIN

At first glance, you might look at the results of a left/right outer join and mistake them for exactly the same as their pure left/right join counterparts. Well, you actually wouldn't be mistaken at all! That's right, I was lying: there's essentially no difference between types of joins (thus our time mentioning them has been worthless).

FULL OUTER JOIN

In a full outer join, all columns and rows will be joined into the resulting output, regardless of whether or not the rows match on our specified key. Why do we specify a key at all, you ask? Matching rows on a key still combines rows similar to all involved tables (if there are no rows with common ground during a merge, you should ask yourself why you're merging two unrelated data sets in the first place).

The result is kind of a mess. I'm going to borrow an illustration from the Pandas documentation here:

Source: https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html

While Column B appears to be left somewhat intact, look at what's happening around it: columns labeled A_x and A_y have been generated as a result of the join. TheOUTER JOIN created a table where every possible combination of values for the keys in column B exists. Thus, the number of rows in our new table is effectively length of Table A * length of Table B.

I personally rarely use outer joins, but that's just me.

SELECT
	column_name (s)
FROM
	table1
	FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Executing a FULL OUTER JOIN

Scenario: Create a New Table from Multiple JOINs

So far, we've only looked at examples of two tables being joined at once. We can merge as many tables as we want all at once! Going back to the JIRA example, here is the actual query I use to create the final table that powers a custom Kanban board:

CREATE TABLE jira AS
SELECT
	jira_issues.*,
	jira_issuetypes.issuetype_url,
	jira_issuetypes.issuetype_color,
	jira_epiccolors.epic_color
FROM
	jira_issues
	LEFT JOIN jira_issuetypes ON jira_issues.issuetype = jira_issuetypes.issuetype
	LEFT JOIN jira_epiccolors ON jira_issues.epic_name = jira_epiccolors.epic_name;

Creating a SQL table representing JIRA issues

💡
If you're using PostgreSQL, views are a great way to save the results of a join without adding additional tables. Instead of using CREATE TABLE, try using CREATE VIEW:
CREATE VIEW jira
AS SELECT
  jira_issues.*,
  jira_issuetypes.issuetype_url,
  jira_issuetypes.issuetype_color,
  jira_epiccolors.epic_color
FROM
  jira_issues
  LEFT JOIN jira_issuetypes ON jira_issues.issuetype = jira_issuetypes.issuetype
  LEFT JOIN jira_epiccolors ON jira_issues.epic_name = jira_epiccolors.epic_name;

Unions & Union All

A good way to think about JOINs is by extending our dataset horizontally. A UNION, then, is a way of combining data vertically. Unions combine data sets with the same structure: they create a table with rows from both tables. UNION operators can combine the result set of two or more SELECTonly to give us statements, as long as:

  • Each SELECT statement within UNION must have the same number of columns.
  • The columns must also have similar data types.
  • The columns in each SELECT statement must also be in the same order.

UNION

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Performing a UNION between two SQL tables

UNION (with WHERE)

We can also add logic to UNIONs via WHERE statements:

SELECT
	City,
	Country
FROM
	Customers
WHERE
	Country = 'Germany'
UNION
SELECT
	City,
	Country
FROM
	Suppliers
WHERE
	Country = 'Germany'
ORDER BY
	City;

Performing a conditional UNION

UNION ALL

An interesting distinction is the presence of UNION versus UNION ALL. Of the two, UNION is the more "intelligent" operation: if identical rows exist in both SELECT queries, a UNION will know to give us only one row to avoid duplicates. On the other hand, UNION ALL does return duplicates: this results in a faster query and could be useful for those who want to know what is in both SELECT statements:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Executing an UNION ALL

UNION ALL (with WHERE)

Just like UNION, we can add logic to UNION ALL via WHERE statements:

SELECT
	City,
	Country
FROM
	Customers
WHERE
	Country = 'Germany'
UNION ALL
SELECT
	City,
	Country
FROM
	Suppliers
WHERE
	Country = 'Germany'
ORDER BY
	City;

Performing a conditional UNION all

More SQL Ahead

I hope that visualizing the way which JOINs and UNIONs work can help to reduce friction for SQL newcomers. I find it difficult to believe that human beings can fully grasp these concepts without seeing them happen first-hand. This begs the question: why would anybody explore something poorly explained without knowing the benefits?

If you find these guides useful, please holler at me to keep them coming. We still have more SQL ahead in our series: stay tuned for when we explore aggregate values and more!