When is it required to give a table name an alias in SQL?

Question

I noticed when doing a query with multiple JOINs that my query didn't work unless I gave one of the table names an alias.

Here's a simple example to explain the point:

This doesn't work:

SELECT subject
from items
join purchases on items.folder_id=purchases.item_id
join purchases on items.date=purchases.purchase_date
group by folder_id

This does:

SELECT subject
from items
join purchases on items.folder_id=purchases.item_id
join purchases as p on items.date=p.purchase_date
group by folder_id

Can someone explain this?

Answer 1

You are using the same table Purchases twice in the query. You need to differentiate them by giving a different name.

You need to give an alias:

• When the same table name is referenced multiple times

Imagine two people having the exact same John Doe. If you call John, both will respond to your call. You can't give the same name to two people and assume that they will know who you are calling. Similarly, when you give the same resultset named exactly the same, SQL cannot identify which one to take values from. You need to give different names to distinguish the result sets so SQL engine doesn't get confused.

Script 1: t1 and t2 are the alias names here

SELECT      t1.col2
FROM        table1 t1
INNER JOIN  table1 t2
ON          t1.col1 = t2.col1

• When there is a derived table/sub query output

If a person doesn't have a name, you call them and since you can't call that person, they won't respond to you. Similarly, when you generate a derived table output or sub query output, it is something unknown to the SQL engine and it won't what to call. So, you need to give a name to the derived output so that SQL engine can appropriately deal with that derived output.

Script 2: t1 is the alias name here.

SELECT col1
FROM
(
    SELECT col1
    FROM   table1
) t1