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:

👉 Every photo currently has a valid user_id that matches a real user.

That is why this worked.

---

🔧 Now Let’s Break It

Now we will insert a new photo.

This photo will have:

• url = 'banner.jpg'

• user_id = NULL

INSERT INTO photos (url, user_id)
VALUES ('banner.jpg', NULL);

What does NULL mean?

It means:

• No user owns this photo

• No user posted this photo

Is this allowed?

Yes.

Even if we have ON DELETE CASCADE, that only applies when deleting a user.

We are not deleting anything.
We are simply inserting a photo that does not reference any user.

This does not break any database rules.

---

▶️ Run the Same Query Again

Now we run this again:

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

The query runs successfully.

But something strange happens.

The new photo (banner.jpg) does NOT appear in the result.

We only see the original 20 photos.

---

❗ Why Did This Happen?

Remember our goal:

Show every photo no matter what.

But now we are not showing all photos.

We are only showing photos that are connected to a user.

So what’s happening?

The problem is that JOIN by default is an INNER JOIN.

An INNER JOIN only returns rows where the condition matches on both tables.

That means:

• If photos.user_id = users.id → show it

• If there is no match → do not show it

• If user_id is NULL → it will not match anything

Very important:

NULL does not equal anything.
Not even another NULL.

So this condition:

photos.user_id = users.id

will fail if user_id is NULL.

And because it fails, that row is removed from the result.

---

🚨 The Real Problem

Our query is no longer fulfilling our goal.

We said:

Show every photo.

But we are actually showing:

Only photos that have a matching user.

This is a big difference.

In real applications, this matters a lot.

Maybe we are:

• Counting total photos

• Calculating total storage

• Showing company-owned photos

If we miss some rows, our logic becomes incorrect.

---

🤔 So What Now?

We need to ask two important questions:

1. Why is that photo not being listed?
   → Because INNER JOIN removes non-matching rows.

2. How can we join photos and users but still show every photo?

That’s what we’ll solve next — by using a different type of join.

---

This is one of the most important lessons in SQL:

Sometimes your query runs successfully…

But it silently removes data you expected to see.

Always make sure your query fulfills your original goal exactly.