Hacker News Reader

Comment by porpoisely | Hacker News Reader

You should never use "SELECT * FROM SomeTable" to retrieve data because the column order isn't guaranteed. Also, you should always use column names to access data.

However, "SELECT * FROM SomeTable" can be used to determine if a table had data ( for "EXISTS" clause ) or to "COUNT" the rows of the table. It's industry standard.

You could also use something like "SELECT 1 FROM SomeTable" but it isn't natural.

View on HN

> You should never use "SELECT * FROM SomeTable" to retrieve data because the column order isn't guaranteed. Also, you should always use column names to access data.

If you always use column names, why does column order matter?

It's not just column order. Sometimes the columns you select affect performance of the query.

More precisely, if you only select indexed columns you can read only from the index rather than read from index then read the rest of columns from table. Usually the indices are entirely in memory as well, so it's buttery smooth, whereas a read from the table will probably require a disk read.

You’re sending more data than necessary across the wire In general and specifically if someone adds a blob data type it can get really hairy.

Another time when you don’t want to do select * is if you are using a database that does columnar storage like Redshift.

Generally for schema bound objects you will want the SELECT 1 anyway, and I find it fairly natural for expression a boolean.

If you want to change it to SELECT 'EXISTS' then I wont be mad :)

Reply on news.ycombinator.com