How come SQL can use an alias twice?

Screen Link:

My Code:

WITH customers_usa_gt_90 AS
( SELECT * FROM customer_usa
 SELECT * FROM customer_gt_90_dollars)
 e.first_name || ' ' || e.last_name employee_name,
 COUNT(c.customer_id) customers_usa_gt_90
 FROM employee e
 LEFT JOIN customers_usa_gt_90 c ON c.support_rep_id = e.employee_id
WHERE e.title == 'Sales Support Agent'

What I expected to happen:
an error because the alias of the subquery is customers_usa_gt_90 and the alias of the 2nd column of the main query is customers_usa_gt_90

What actually happened: it worked.

How come SQL can use the same alias twice? Once in a subquery and once as an alias of a column? How does SQL not get confused in the portion of the code when I am executing an LEFT JOIN? It’s slightly confusing because of the nature of Python (obviously a different language) where you store a variable or “use an alias”

1 Like

Hi @la.cayabyab:

An alias is basically like a short form of a table name. That being said, aliases can be used mutiple times to reference the specific column from that table (since there are multiple properties [in the form of multiple columns] in the table). So e.employee_id and e.title are shortforms of employee.employee_id and employee.title. Note that aliases can be used in the SELECT statement too even though it is placed before the definition of the alias (i.e. employee e) because what SQL actually does is it goes to the table and “sees” that the user is referencing that particular column from the database table to be returned and displayed to the user.

Hope this clarifies.

1 Like

When you say subquery, do you mean Common Table Expression instead? The former is defined within an outer query while the latter is defined first using WITH before any query is written.

There are 2 types of alias. Table alias and Column alias. I can’t find definitive proof but seems they can use the same name and sql knows whether you refer to table or column based on sql syntax (position where each clashing alias appears).

On a similar note, i found this long discussion (i haven’t fully digested) showing how same alias can be used for different tables:

1 Like