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: man, woman and love. Let’s look at each of them, starting with the first two.

The 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 ;

The woman table has the same setup. Let’s see who Gary and Sara’s women friends are:

SELECT id, name FROM woman ;

The 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 love and man tables. Specifically, it uses an INNER JOIN, but you don’t have to include the INNER keyword. 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: love, man, and 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 man.name and 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 man and 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 JOIN conditions.

Now, you’ve surely noticed that there are two JOIN statements. The first JOIN works with the man table and compares the id from 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!