Missing Data in Joins (PostgreSQL + Supabase)

In the last lesson, I mentioned two things in a very mysterious way:

1. Sometimes the ordering of FROM and JOIN matters. 2. There are different kinds of joins available in SQL.

Now we’re going to understand this in a very practical way.

We will write a simple join query.

It will work perfectly.

Then we will make a very small change in our table.

And suddenly… the query will not behave the way we expect.

🎯 Our Goal

We want to:

• Show each photo’s URL
• Show the username of the person who posted it
• And most importantly:
• We must show every single photo, no matter what

No matter how many photos exist.

No matter what state they are in.

Every photo must appear in the result.

🧠 How We Think About the Join

We start with the photos table.

Then for each user_id inside photos, we look into the users table.

If we find a matching users.id, we attach that user’s data.

From all that combined data, we only care about:

• url
• username

So we ignore everything else.

Simple.

✅ Writing the Query

Here’s the query:

SELECT url, username
FROM photos
JOIN users
ON photos.user_id = users.id;

We run it.

It works perfectly.

We see all 20 photos.

Each photo has a username.

Everything looks correct.

But here is something very important: