sql joins: moving in together

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

1. Inner Join: The Love Match
2. Left Join: The Bossy Guy
3. Right Join: The Bossy Girl
4. 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.