WITH customers_usa_gt_90 AS ( SELECT * FROM customer_usa INTERSECT SELECT * FROM customer_gt_90_dollars) SELECT 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' GROUP BY 1 ORDER BY 1
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”