PostgreSQL - JOINS

Hello, aspiring database enthusiasts! Today, we're going to embark on an exciting journey through the world of PostgreSQL JOINs. As your friendly neighborhood computer teacher, I'm here to guide you through this adventure, step by step. Don't worry if you've never written a line of code before – we'll start from the very basics and work our way up. So, grab your virtual hard hats, and let's dive in!

What are JOINs?

Before we jump into the different types of JOINs, let's understand what a JOIN actually is. Imagine you're planning a big party (because who doesn't love a good database party, right?). You have two lists: one with your friends' names and another with their favorite drinks. JOINs are like magical party planners that help you combine these lists in various ways to create the perfect guest list with everyone's preferred beverages.

In database terms, JOINs allow us to combine rows from two or more tables based on a related column between them. Now, let's explore the different types of JOINs PostgreSQL offers us.

The CROSS JOIN

What is a CROSS JOIN?

A CROSS JOIN is like inviting everyone on both of your lists to the party, regardless of whether you know their drink preference or not. It combines every row from the first table with every row from the second table.

CROSS JOIN Example

Let's create two simple tables for our example:

CREATE TABLE friends (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE drinks (
    id SERIAL PRIMARY KEY,
    drink VARCHAR(50)
);

INSERT INTO friends (name) VALUES ('Alice'), ('Bob'), ('Charlie');
INSERT INTO drinks (drink) VALUES ('Cola'), ('Juice'), ('Water');

Now, let's perform a CROSS JOIN:

SELECT f.name, d.drink
FROM friends f
CROSS JOIN drinks d;

Result:

name	drink
Alice	Cola
Alice	Juice
Alice	Water
Bob	Cola
Bob	Juice
Bob	Water
Charlie	Cola
Charlie	Juice
Charlie	Water

As you can see, every friend is paired with every drink. It's like saying, "Hey, everyone can try all the drinks!" This might not always be practical, but it's a great way to generate all possible combinations.

The INNER JOIN

What is an INNER JOIN?

An INNER JOIN is more selective. It's like matching your friends with their favorite drinks, but only including those friends who have a known drink preference.

INNER JOIN Example

Let's modify our tables a bit:

CREATE TABLE friends (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE favorite_drinks (
    id SERIAL PRIMARY KEY,
    friend_id INTEGER,
    drink VARCHAR(50)
);

INSERT INTO friends (name) VALUES ('Alice'), ('Bob'), ('Charlie'), ('David');
INSERT INTO favorite_drinks (friend_id, drink) VALUES (1, 'Cola'), (2, 'Juice'), (3, 'Water');

Now, let's perform an INNER JOIN:

SELECT f.name, fd.drink
FROM friends f
INNER JOIN favorite_drinks fd ON f.id = fd.friend_id;

Result:

name	drink
Alice	Cola
Bob	Juice
Charlie	Water

Notice that David doesn't appear in the result because he doesn't have a favorite drink listed.

The LEFT OUTER JOIN

What is a LEFT OUTER JOIN?

A LEFT OUTER JOIN is like making sure all your friends are on the guest list, even if you don't know their drink preference. Those without a known preference might get a default drink or no drink at all.

LEFT OUTER JOIN Example

Using the same tables as in the INNER JOIN example:

SELECT f.name, fd.drink
FROM friends f
LEFT OUTER JOIN favorite_drinks fd ON f.id = fd.friend_id;

Result:

name	drink
Alice	Cola
Bob	Juice
Charlie	Water
David	NULL

See how David is included now, but with a NULL drink? That's the magic of LEFT OUTER JOIN!

The RIGHT OUTER JOIN

What is a RIGHT OUTER JOIN?

A RIGHT OUTER JOIN is the opposite of LEFT OUTER JOIN. It's like making sure all drinks are on the menu, even if no one has chosen them as their favorite.

RIGHT OUTER JOIN Example

Let's add a drink that no one has chosen as their favorite:

INSERT INTO favorite_drinks (drink) VALUES ('Lemonade');

SELECT f.name, fd.drink
FROM friends f
RIGHT OUTER JOIN favorite_drinks fd ON f.id = fd.friend_id;

Result:

name	drink
Alice	Cola
Bob	Juice
Charlie	Water
NULL	Lemonade

Lemonade appears in the list even though no friend has chosen it as their favorite.

The FULL OUTER JOIN

What is a FULL OUTER JOIN?

A FULL OUTER JOIN is the party planner's dream. It includes all friends and all drinks, matching them where possible and using NULL where there's no match.

FULL OUTER JOIN Example

SELECT f.name, fd.drink
FROM friends f
FULL OUTER JOIN favorite_drinks fd ON f.id = fd.friend_id;

Result:

name	drink
Alice	Cola
Bob	Juice
Charlie	Water
David	NULL
NULL	Lemonade

This gives us the complete picture: all friends (even David without a favorite drink) and all drinks (even Lemonade without a friend who favors it).

Conclusion

And there you have it, folks! We've journeyed through the land of PostgreSQL JOINs, from the all-inclusive CROSS JOIN to the comprehensive FULL OUTER JOIN. Remember, choosing the right JOIN is like picking the perfect mix for your party – it all depends on what information you need and how you want to combine your data.

Practice these JOINs, play around with different scenarios, and soon you'll be the life of the database party! Who knew managing data could be so much fun, right? Until next time, happy querying!

JOIN Type	Description
CROSS JOIN	Combines every row from the first table with every row from the second table
INNER JOIN	Returns rows when there is a match in both tables
LEFT OUTER JOIN	Returns all rows from the left table, and the matched rows from the right table
RIGHT OUTER JOIN	Returns all rows from the right table, and the matched rows from the left table
FULL OUTER JOIN	Returns rows when there is a match in one of the tables

Credits: Image by storyset