I need help with proper SQL terminology for my documentation. When I write a query like this:
select emp.name, mgr.name
from employees emp
inner join employees mgr
on emp.manager_id = mgr.employee_id
I have one physical table called ‘employees’ but I’m using it twice with different names (‘emp’ and ‘mgr’). What’s the technical term for these named references?
How should I complete this sentence: “This query references one table (employees) but creates two ___ (emp and mgr)”
I want to make sure I use the right terminology in my documentation.
great question! i often think about aliases like ‘emp’ and ‘mgr’ too. do you think they really make it clearer for other devs? also, i’m curious, does using aliases like that affect performance compared to subqueries?
You’re looking for table aliases. In your example, ‘emp’ and ‘mgr’ are table aliases - they’re just alternative names for the same physical table within your query. So your sentence would be: “This query references one table (employees) but creates two table aliases (emp and mgr)”. Aliases do more than just shorten table names. They’re actually required for self-joins like yours because you need to tell the database which version of the employees table you’re talking about in your joins and column selections. Without them, the database engine gets confused about which instance you mean. This works the same way across MySQL, PostgreSQL, SQL Server, and Oracle.
yep, those are called aliases (or correlation names in formal SQL docs). you’re basically making temp refs to tell apart the two instances of the employees table in your self-join. without them, the query would be ambiguous and throw errors.