4 ms·
> Honestly, I think AS should basically never be used for tables. I believe AS is required for self-joins. A common expository query is to find all employees w
by quizotic 10y ago
> Honestly, I think AS should basically never be used for tables.
I believe AS is required for self-joins. A common expository query is to find all employees who earn more than their managers, which can be expressed via "select name from staff as employee join staff as manager on (employee.manager_id = manager.id) where employee.salary > manager.salary".
Also, SQL allows a sub-select to be listed in the FROM clause. But in such cases, the sub-select must be given a name (tables have a name, but sub-selects don't).
As an aside, the keyword "AS" is allowed by not required.
- Stasis5001 10y agoSure, I forgot the self-join case. In my experience these are a minority of joins, but definitely. In postgres at least, a lot of self-joins can be solved with a DISTINCT ON instead. Sub-selection with a FROM clause, yeah, those definitely need names. But those don't have perfectly good names that everybody already knows, whereas tables do. If you're maintaining somebody's code and they declare foo_bar = 10 I think it'd be pretty strange to ever consider writing fb = foo_bar just because you find foo_bar too long to write or something.
- thomasthomas 10y agowhy is "tb_" bad for tables? for some reason this has become standard practice for me. at least on SQL Server it can help distinguish tables from views in complex queries or stored procedures. No idea where i learned this habit.