We’ve now written several JOIN queries.

Before moving forward, there are some very important things you need to understand about JOIN syntax.

These small details will save you from confusion later — especially when queries become complex.

Let’s go step by step.

---

1️⃣ Does Table Order Matter in JOIN?

Sometimes yes. Sometimes no.

Consider this query:

SELECT *
FROM comments
JOIN photos
  ON photos.id = comments.photo_id;

Now flip it:

SELECT *
FROM photos
JOIN comments
  ON photos.id = comments.photo_id;

In many cases, you’ll get the same result.

But in some cases (especially when we talk about LEFT JOIN and RIGHT JOIN), the order will change the result.

For now, remember this:

Table order sometimes matters — and later, we’ll see exactly when.

---

2️⃣ Ambiguous Column Names

This is one of the most common beginner mistakes.

Let’s imagine we join comments and photos.

Both tables have a column named:

id

After the JOIN, our temporary result table looks like:

id	contents	photo_id	id	url

Now if we write:

SELECT id
FROM comments
JOIN photos
  ON photos.id = comments.photo_id;

We get an error:

column reference "id" is ambiguous

Why?

Because SQL doesn’t know which id you mean:

• comments.id ?

• photos.id ?

---

✅ The Solution: Be Explicit

You must specify the table name.

SELECT comments.id
FROM comments
JOIN photos
  ON photos.id = comments.photo_id;

Or:

SELECT photos.id

Now SQL knows exactly which column you want.

---

3️⃣ Selecting Both IDs

You can even select both:

SELECT comments.id, photos.id
FROM comments
JOIN photos
  ON photos.id = comments.photo_id;

But the result might show duplicate column names.

To fix that, we rename them using AS.

---

4️⃣ Renaming Columns Using AS

SELECT comments.id AS comment_id,
       photos.id AS photo_id
FROM comments
JOIN photos
  ON photos.id = comments.photo_id;

Now your result is clean and readable:

comment_id	photo_id

This is very important in real-world applications.

---

5️⃣ Renaming Tables (Table Aliases)

Sometimes queries become long.

Instead of writing:

FROM photos
JOIN comments

We can shorten it:

FROM photos AS p
JOIN comments AS c

Now everywhere we reference the table, we use:

p.id
c.photo_id

Full example:

SELECT c.id, p.url
FROM photos AS p
JOIN comments AS c
  ON p.id = c.photo_id;

This is cleaner and easier to read.

---

Can We Remove AS?

Yes.

Instead of:

FROM photos AS p

You can write:

FROM photos p

Both work the same.

However, using AS makes things clearer for beginners.

---

Important Rule

You only replace table references.

You do NOT replace column names.

Example:

If the column is:

photo_id

You cannot rename that automatically.

Aliases only apply to table names.

---

6️⃣ Why This Matters for Bigger Queries

When you start writing:

• 3 table joins

• 4 table joins

• Nested joins

• JOIN + GROUP BY

Table aliases become extremely important.

Example of a complex query:

SELECT u.username, COUNT(c.id) AS total_comments
FROM users AS u
JOIN comments AS c
  ON u.id = c.user_id
GROUP BY u.username;

Imagine writing full table names everywhere.

It becomes messy quickly.

---

Summary for Students

Here are the key takeaways:

✅ Table order sometimes matters

✅ If column names are duplicated, specify table name

✅ Use AS to rename columns

✅ Use AS to rename tables

✅ Aliases make complex queries readable