4 ms·

"rather than using SELECT . If you do use SELECT , don't assume that the fields are in a particular order." There are professional developers that use Select *

"rather than using SELECT . If you do use SELECT , don't assume that the fields are in a particular order." There are professional developers that use Select * EVER! Maybe add write a simple Select, Insert and Update Statement to the FizBuzz test.

mschuster91 8y ago
> There are professional developers that use Select * EVER! Old code from the 2000 PHP era is full of this. Never underestimate the horrors of "legacy" code bases, and never underestimate what people do in short lived "maintenance scripts" or "helpers" that are used well beyond the intended life span. Or "proofs of concept" aka "unbelievably dirty hacks to get a MVP" that end up being extended and extended to being the "real product" in the end. For an MVP I will always use "select *" and friends simply for speed of development, but clearly communicate that this is in no way intended for production usage.
- lowercased 8y ago
  > clearly communicate that this is in no way intended for production usage. But it likely will be anyway. I use a variety of ORMs and hand-rolled SQL, and often will do select . The times when I know there may be a performance impact, or I'm debugging an existing performance impact, hand-rolled SQL always is on the table, but often* the issue is either missing/poor indexes or simply table design that didn't foresee the current use case. There's been a couple of times where a stray select * was, really, pulling back unintended very large blobs and causing a performance issue, but it's been infrequent compared to index issues.
porpoisely 8y ago
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.
- hobs 8y ago
  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 :)
- twic 8y ago
  > 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?
  - 0xFACEFEED 8y ago
    It's not just column order. Sometimes the columns you select affect performance of the query.
    - deleted 8y ago
      [deleted]
    - deleted 8y ago
      [deleted]
    - greenleafjacob 8y ago
      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.
  - scarface74 8y ago
    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.
salawat 8y ago
Don't throw out the baby with the bath water. Just be aware of invariants, and respect them. There is nothing wrong with a select * when working with an unfamiliar data model for initial rough development to get your head around the data path. Just remember to refine your query to meet your performance requirements.
sbov 8y ago
This advice always struck me as funny, because an ORM will select every column in a table too, even ones you aren't going to use. So the corollary to this is: never use an ORM. One would catch a lot of debate (never use an ORM), the other mostly doesn't (never use select *). Maybe most people that use an ORM don't connect these dots though.
- insomniacity 8y ago
  Which ORMs please? I'd like to avoid them! Hibernate doesn't for starters.
  - bmdavi3 8y ago
    Django's ORM doesn't select *, but it will select every column unless you tell it otherwise.
- tracker1 8y ago
  IMHO if you are using a scripted language, I find it easier to NOT use an ORM/ODM layer. function lookupFoo(baz) { const sql = await db.init(); const result = await sql.query` SELECT x, y, z FROM foo WHERE bar = ${baz} `; return result.records.map(mapResultsToFoo)[0]; }
  - vkou 8y ago
    I really hope that sql.query does proper escaping, because otherwise, you're going to have a jolly time, when Bobby Drop Tables signs up for your service.

mschuster91 8y ago

> There are professional developers that use Select * EVER! Old code from the 2000 PHP era is full of this. Never underestimate the horrors of "legacy" code bases, and never underestimate what people do in short lived "maintenance scripts" or "helpers" that are used well beyond the intended life span. Or "proofs of concept" aka "unbelievably dirty hacks to get a MVP" that end up being extended and extended to being the "real product" in the end. For an MVP I will always use "select *" and friends simply for speed of development, but clearly communicate that this is in no way intended for production usage.

lowercased 8y ago
> clearly communicate that this is in no way intended for production usage. But it likely will be anyway. I use a variety of ORMs and hand-rolled SQL, and often will do select . The times when I know there may be a performance impact, or I'm debugging an existing performance impact, hand-rolled SQL always is on the table, but often* the issue is either missing/poor indexes or simply table design that didn't foresee the current use case. There's been a couple of times where a stray select * was, really, pulling back unintended very large blobs and causing a performance issue, but it's been infrequent compared to index issues.

porpoisely 8y ago

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.

hobs 8y ago
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 :)
twic 8y ago
> 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?
- 0xFACEFEED 8y ago
  It's not just column order. Sometimes the columns you select affect performance of the query.
  - deleted 8y ago
    [deleted]
  - deleted 8y ago
    [deleted]
  - greenleafjacob 8y ago
    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.
- scarface74 8y ago
  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.

salawat 8y ago

Don't throw out the baby with the bath water. Just be aware of invariants, and respect them. There is nothing wrong with a select * when working with an unfamiliar data model for initial rough development to get your head around the data path. Just remember to refine your query to meet your performance requirements.

sbov 8y ago

This advice always struck me as funny, because an ORM will select every column in a table too, even ones you aren't going to use. So the corollary to this is: never use an ORM. One would catch a lot of debate (never use an ORM), the other mostly doesn't (never use select *). Maybe most people that use an ORM don't connect these dots though.

insomniacity 8y ago
Which ORMs please? I'd like to avoid them! Hibernate doesn't for starters.
- bmdavi3 8y ago
  Django's ORM doesn't select *, but it will select every column unless you tell it otherwise.
tracker1 8y ago
IMHO if you are using a scripted language, I find it easier to NOT use an ORM/ODM layer. function lookupFoo(baz) { const sql = await db.init(); const result = await sql.query` SELECT x, y, z FROM foo WHERE bar = ${baz} `; return result.records.map(mapResultsToFoo)[0]; }
- vkou 8y ago
  I really hope that sql.query does proper escaping, because otherwise, you're going to have a jolly time, when Bobby Drop Tables signs up for your service.