Just in time for Valentine’s Day, we discover which of Gary and Sara’s friends are in love – and how to use the SQL JOIN statement to work with data in multiple tables!
Did you know SQL is a matchmaker? That’s right; it loves to make data couples by linking information from two (or more!) tables using the
JOIN statement. However, many SQL rookies run into problems using
JOIN. So let’s go to a party and learn how to use
JOIN the right way!
Meet Sara and Gary, the hosts of our party. They have male and female friends. They know some of their friends are couples, but they can’t remember which ones. Also – somewhat weirdly – they keep friend information in a database. Can we use it to help our quirky hosts avoid seating the wrong couples together?
Man + Woman = Love
All the data about Sara and Gary’s friends is stored in three tables:
love. Let’s look at each of them, starting with the first two.
man table consists of two columns: an identifying number (
id) and a man’s name (
name). Obviously, each man will have one record in this table. Let’s see who Sara and Gary’s men friends are:
SELECT id, name FROM man ;
woman table has the same setup. Let’s see who Gary and Sara’s women friends are:
SELECT id, name FROM woman ;
love table also contains just two records, but it’s set up a bit differently. All we have are ID numbers for the men (
man_id) and women (
woman_id). These two ID numbers make one record, which represents a couple. Have a look:
As you probably can guess, the
man_id field relates to the
id record in the
man table and the “woman_id” field relates to the “id” record in the “woman” table. What an easy way to find out if a man and woman are in love!
But wait! What if you want to know which men are in love? And what about finding out the couples’ names?
Using JOIN to Find the Other Half
First, let’s find out which men are in love. Here is the query we’ll use:
SELECT id, name FROM love JOIN man ON love.man_id=man.id;
This query uses
JOIN to find matching records in the
man tables. Specifically, it uses an
INNER JOIN, but you don’t have to include the
INNER JOIN returns only the records from the first table (
man) that have a match in the second table (
love). What determines this match? The stated fields in two or more tables, which are specified by the
ON operator, must have exactly equal values. In this case,
ON tests if an ID from the
man table (
man.id – notice the dot, which tells SQL the table to search in) equals the ID found in the
man_id field of the
love table (
love.man_id). If it does, it will be shown in the results.
This is what the query returns:
So now we know which men are in love. Who are they in love with?
Using JOIN to Find Couples
To combine both men and women into couples, we need records from three tables:
woman. Here’s the query we’ll use:
SELECT man.name + ‘ and ‘ + woman.name FROM love JOIN man ON love.man_id=man.id JOIN woman ON love.woman_id=woman.id;
Let’s analyze the query piece by piece.
First, notice that in the
SELECT statement we combined the table and column names – we used
woman.name. This is because these columns have the same name in both tables.
Next, you’ll see a couple of plus signs and spaces,
‘ and ‘. We are telling the database to display the
name fields from the
woman tables, with an “and” in between each pair.
Next, there is the
FROM. We place
love after the
FROM because the identifiers stored in this table are used in the
Now, you’ve surely noticed that there are two
JOIN statements. The first
JOIN works with the
man table and compares the
man with the
man_id field from
love. The second
JOIN compares an
id from the
woman table with
woman_id from the
love table. When a match is found, the man or woman’s name is displayed. This happens twice for each record (once for the man, once for the woman) because two ID fields are stored in each record, which gives us couples!
Look at the result table:
Amongst Sara and Gary’s friends, there are four couples: John and Mila, Jeremy and Layla, Ryan and Nora, and Mark and Aria. Thanks to SQL and
JOIN, we know who loves who – and who should sit together!
Give SQL JOIN Some Love!
Combining data from multiple tables is common in SQL. Therefore, knowing how to use all the various JOIN operators is very useful indeed! In this article, we used the INNER JOIN, but there are several more! You can learn more about them in Vertabelo Academy’s SQL Basics course or in the “Illustrated Guide” series, which explains INNER JOINs, OUTER JOINs (including LEFT JOIN, RIGHT JOIN, and FULL JOIN), and CROSS JOINs, as well as self-joins, non-equi joins, and multiple joins. Check it out!