nonobie — Real dev skills, explained like a friend would

Warming up the neural circuits...

What you'll learn

In the previous module, we learned how to split our data to keep it clean. Now, we learn how to put it back together for our users. By the end of this module, you'll be able to:

Show a post along with its author's name
Find users who have never written a post
Build a single query that pulls data from 3, 4, or even 5 different tables

1. What is a JOIN?

A JOIN is a way to combine rows from two or more tables based on a related column between them (usually a Primary Key and a ).

Table Relationships (The Bridge)

2. INNER JOIN — The "Matchmaker"

The INNER JOIN returns only the rows where there is a match in both tables.

sql

SELECT posts.title, authors.name
FROM posts
INNER JOIN authors ON posts.author_id = authors.id;

Result: You see all posts that have a valid author. If a post has no author, or an author has no posts, they are hidden.

3. LEFT JOIN — The "Inclusive" Join

The LEFT JOIN returns all rows from the "Left" table (the one after FROM), even if there are no matches in the "Right" table.

sql

SELECT authors.name, posts.title
FROM authors
LEFT JOIN posts ON authors.id = posts.author_id;

Result: You see every author. If an author has no posts (like a new user), you'll see their name and a NULL for the post title.

Tip

When to use: Use a LEFT JOIN when you want to find "empty" or "orphaned" data. For example: "Show me all customers and their orders, including customers who haven't ordered yet."

4. Joining Multiple Tables

In real apps, you often need data from many places. You can chain JOIN commands together.

sql

-- Get Post Title, Author Name, AND Category Name
SELECT 
    posts.title, 
    authors.name AS author, 
    categories.name AS category
FROM posts
JOIN authors ON posts.author_id = authors.id
JOIN categories ON posts.category_id = categories.id

Watch out

Performance Tip: Every JOIN makes the database work harder. While Postgres can handle dozens of joins, try to only join the tables you actually need for that specific request.

5. Visual Guide to Joins

Join Type	Description	Frequency in Industry
INNER JOIN	Only records that match in both tables.	90% of your work.
LEFT JOIN	Everything from Table A + matches from Table B.	9% of your work.
RIGHT JOIN	Everything from Table B + matches from Table A.	Rare (usually just rewrite as a LEFT JOIN).
FULL JOIN	Everything from both tables.	Very Rare.

Choosing the Right JOINflowchart

Rendering diagram…

Practice Exercise

Using your instructors and lessons tables:

Write an INNER JOIN to show each lesson title and its instructor's name.
Write a LEFT JOIN to show all instructors, even those who haven't been assigned a lesson yet.
Advanced: Create a categories table. Link it to lessons. Write a query that joins all 3 tables to show: Lesson Title | Instructor Name | Category Name.

Note

Done? You've just mastered the hardest part of . You now have the skills to query complex, professional-grade databases. In the final module, we'll put everything together into a portfolio project.