In our coding bootcamp, as we breezed past SQL week, one of the topics that was harder to grasp was that of joins. When I looked at the fifth Venn diagram representation on a Google Image search in an attempt to better to visualize the concept then, it was then that I pictured a couple moving in and how that ties into SQL joins. 💖🏠💕
When a couple decides to move in together, the things they bring into their shared space represent how data from two tables (the guy and girl) merge through different types of SQL joins. The "love" they have for certain items (data) determines what ends up in the shared house.
Let's start off with a visual of these two tables between John and Emily (a hypothetical couple), and what they each love.
Main Tables: The Guy and the Girl
Guy Table (John's Interests)
| Guy | GuyLove |
|---|---|
| John | Movies |
| John | Music |
| John | Sports |
Girl Table (Emily's Interests)
| Girl | GirlLove |
|---|---|
| Emily | Movies |
| Emily | Music |
| Emily | Books |
Table of Contents
- Inner Join: The Love Match
- Left Join: The Bossy Guy
- Right Join: The Bossy Girl
- Full Join: The Cantankerous Couple
Inner Join: The Love Match
When a couple shares mutual love for the same things, only those items make it into the house. If either doesn't love something, it stays out.
Analogy: They both only move in items they both love.
*SQL Code: *
SELECT *
FROM guy
INNER JOIN girl
ON guy.GuyLove = girl.GirlLove;
Result Table:
| Guy | GuyLove | Girl | GirlLove |
|---|---|---|---|
| John | Movies | Emily | Movies |
| John | Music | Emily | Music |
Explanation:
An inner join finds the "love match" between the guy and girl, meaning it only brings in records where there’s a common interest between both. In our case, John and Emily both love movies and music, so only those shared loves end up in the house.
Left Join: The Bossy Guy
In this case, the guy is bossy. He brings all of his stuff into the house, even if the girl doesn’t love it.
Analogy: The guy brings all his stuff when they move in together, regardless of whether the girl loves it or not.
*SQL Code: *
SELECT *
FROM guy
LEFT JOIN girl
ON guy.GuyLove = girl.GirlLove;
Result Table:
| Guy | GuyLove | Girl | GirlLove |
|---|---|---|---|
| John | Movies | Emily | Movies |
| John | Music | Emily | Music |
| John | Sports | NULL | NULL |
Explanation:
A left join returns all records from the guy’s table (left table aka first table specified in FROM), regardless of whether there’s a match in the girl’s table (right table). So, even though John’s love for sports doesn’t match any of Emily’s interests, it still shows up in the final result.
While John has an interest in "Sports," Emily does not share that interest, so there’s no matching value- hence we see a NULL for her in that record.
Right Join: The Bossy Girl
Here, the roles are reversed. The girl is the bossy one, and she brings all of her stuff into the house regardless of the guy's feelings.
Analogy: The girl brings all her stuff into the house, whether or not the guy loves it.
*SQL Code: *
SELECT *
FROM guy
RIGHT JOIN girl
ON guy.GuyLove = girl.GirlLove;
Result Table:
| Guy | GuyLove | Girl | GirlLove |
|---|---|---|---|
| John | Movies | Emily | Movies |
| John | Music | Emily | Music |
| NULL | NULL | Emily | Books |
Explanation:
A right join prioritizes the girl’s table, meaning all of her interests get included, even if they don’t match with the guy’s interests. In this case, John and Emily share a love for movies and music so that record shows up in both.
However, since John does not love shopping, it appears as a new record with NULL values for John's columns, indicating he has no interest in it.
Full Join: The Cantankerous Couple
In this scenario, both the guy and girl are very stubborn and bossy. They each bring everything they love into the house, whether or not the other person loves it. No compromise here!
Analogy: They both bring all their stuff into the house, regardless of whether the other loves it or not.
*SQL Code: *
SELECT *
FROM guy
FULL OUTER JOIN girl
ON guy.GuyLove = girl.GirlLove;
Result Table:
| Guy | GuyLove | Girl | GirlLove |
|---|---|---|---|
| John | Movies | Emily | Movies |
| John | Music | Emily | Music |
| John | Sports | NULL | NULL |
| NULL | NULL | Emily | Books |
Explanation:
In this full join example, we see that John brings in his love for sports, while Emily brings her love for books. The NULL values in the Girl and GirlLove columns indicate that there were no corresponding entries in John’s interests for these loves, and vice versa.
This full join captures all the items from both tables, showing how both John and Emily fill the house with their loves, whether shared or not.
Top comments (0)