Intro to Data Joining: JOIN

Today we're diving into one of the coolest topics in working with relational databases — joining data from multiple tables using JOIN. It's a powerful tool that opens the door to building complex relationships and analytics.

Imagine your database is a giant comic book, where each separate table is its own panel. To get the full story, you gotta connect those panels. That's exactly what JOIN helps you do. Data joining is the process that lets a query pull info from several tables and link it up based on certain conditions.

Relational databases are built around the concept of relationships between tables. Each table stores info about a specific entity, and to get the full picture, we often want to link data from different tables. For example:

• The students table has a list of students.
• The courses table stores a list of courses.
• The enrollments table shows who is taking which courses.

To find out which student is taking which course, we need to join these tables. In job interviews, a dev who knows how to use JOIN looks pretty solid, since it's one of the most in-demand data skills out there.

Main Types of Joins

PostgreSQL has a few types of JOIN, and each one is made for a specific job. Let's check them out in general terms, so you don't have to stress about tricky examples just yet:

Which JOIN you pick depends on your task:

• If you only want matching data from both tables, use INNER JOIN.
• If you want to keep all data from one table, and only matches from the other, go with LEFT JOIN or RIGHT JOIN.
• If you need all data from both tables, even if they don't match, use FULL OUTER JOIN.

Let's break down all these joins with some hands-on practice.

Sample Task — "Who's in Which Course?"

Let's say we've got three tables:

The students table

The courses table

The enrollments table

These tables are linked like this:

• The id field in students is the unique student ID.
• The id field in courses is the unique course ID.
• In the enrollments table, the student_id and course_id columns create the link between students and courses.

Let's say we need to answer this question: which student is enrolled in which course?

You can get the answer with a JOIN. We'll join the tables based on their relationships:

• Join students and enrollments on id = student_id.
• Join enrollments and courses on course_id = id.

Here's what the SQL query looks like:

SELECT students.name, courses.title
FROM enrollments
JOIN students ON enrollments.student_id = students.id
JOIN courses ON enrollments.course_id = courses.id;

What this query does:

• FROM enrollments — we start with the table that links students and courses.
• JOIN students ON enrollments.student_id = students.id — we join in the students to get their names.
• JOIN courses ON enrollments.course_id = courses.id — we join in the courses to get the titles.

The result will look like this:

If something in this query doesn't make sense yet, don't sweat it! Figuring this stuff out is what the next lectures are for!

Why Does This Matter in Real Life?

Now that you get how to join tables, let's talk about the real world. Ever wondered how an online store works? Like, when you pick a smartphone and see reviews from other users.

1. The products table has info about products.
2. The reviews table has customer reviews.
3. The customers table has info about the customers themselves.

To show reviews on the site, you gotta join the products, reviews, and customers tables. That's JOIN in action.

What Should You Watch Out For?

Before we go deeper into JOIN in the next lectures, here are a couple things to keep in mind:

1. Order in JOIN matters. For example, LEFT JOIN returns rows from the left table, so if you switch the order, you change the result.
2. Work with small tables. At first, avoid querying millions of rows. Remember, even the simplest query should be easy to understand.
3. Get used to thinking about relationships. Once you start seeing tables as parts of one big structure, working with JOIN will feel natural.

Starting today, we're on our way to becoming SQL pros. In the next lecture, you'll learn how to use INNER JOIN to pull data from two tables. That's gonna be a big step in your PostgreSQL journey.

2

Task

SQL SELF, level 11, lesson 0

Locked

Using COALESCE to handle NULL in strings

Using COALESCE to handle NULL in strings

Open

2

Task

SQL SELF, level 11, lesson 0

Locked

Calculating average price with condition and NULL handling

Calculating average price with condition and NULL handling

Open