LEFT JOIN

The order of the tables matters in a LEFT JOIN, because the table immediately after FROM is always the table whose rows are preserved.

1. First query

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

Here:

• photos = left table
• users = right table
• Every row from photos is preserved.
• A matching user is added when one exists.

Your data contains:

photos.id	url	user_id
1	santina.net	2
2	alayna.net	3
3	kailyn.name	1
4	banner.jpg	NULL

Users:

users.id	username
1	Reyna.Marvin
2	Micah.Cremin
3	Alfredo66
4	Gerard_Mitchell42

The result is:

url	username
santina.net	Micah.Cremin
alayna.net	Alfredo66
kailyn.name	Reyna.Marvin
banner.jpg	NULL

Why is banner.jpg still there?

Because every photos row must survive the LEFT JOIN.

There is no user_id for banner.jpg, so PostgreSQL cannot find a matching user. It therefore fills the users columns with NULL.

---

2. Second query

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

Now the situation is reversed:

• users = left table
• photos = right table
• Every row from users is preserved.

The result is:

username	url
Reyna.Marvin	kailyn.name
Micah.Cremin	santina.net
Alfredo66	alayna.net
Gerard_Mitchell42	NULL

This time, Gerard_Mitchell42 is preserved because users is the left table.

There is no photo belonging to user 4, so PostgreSQL gives NULL for the photo columns.

---

The key idea

Think of LEFT JOIN as:

FROM LEFT_TABLE
LEFT JOIN RIGHT_TABLE

means:

KEEP ALL rows from LEFT_TABLE
TRY to find matching rows in RIGHT_TABLE

So:

FROM photos
LEFT JOIN users

means:

Keep all photos. Find users when possible.

Whereas:

FROM users
LEFT JOIN photos

means:

Keep all users. Find photos when possible.

A useful mental model

photos                         users
  ↓                              ↓
KEEP ALL                    KEEP ALL
  ↓                              ↓
LEFT JOIN users             LEFT JOIN photos

Therefore, these two queries are not equivalent:

FROM photos
LEFT JOIN users

and

FROM users
LEFT JOIN photos

They would produce the same matched records, but they preserve different unmatched records.

---

One more important point

If you changed LEFT JOIN to INNER JOIN:

FROM photos
INNER JOIN users
    ON users.id = photos.user_id;

then the order would not affect which matching rows are returned in the same way. Both tables must have a match, so banner.jpg and Gerard_Mitchell42 would disappear.

The diagram is therefore specifically demonstrating why table order matters with LEFT JOIN.