Comment by quizotic | Hacker News Reader

Comment by quizotic | Hacker News Reader

Parent Full thread

> 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.

Sure, 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.

why 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.

Reply on news.ycombinator.com