One thing I don't like about using `SELECT *` in code is that it hides information from the your future self. When I come back to a piece of code in a couple of years time, knowing which columns I'm selecting helps provide context for the code using that query.
Yes. But there are times it is by design for the sql query to be agnostic about the database structure or the data model. That's when `SELECT *` becomes very useful.
SQL queries really need to separate out the "run the query" from the "now send me these details about the results" part.
In many cases, I'd like that latter data transfer stage to be smarter. Give me back some smart object which represents a result set, but then lazily only retrieves the information my code actually uses. At the same time, try to predict what information I'll be needing from the result set to avoid server round trips. (If I'm iterating through a result set in order accessing 4 of the 8 columns, you can probably just batch send the data my code uses).
Which would be possible, but not without the server round trips that he also seems to want to get rid of.
Basically, he wants a bunch of complexity and heuristics to be pushed into the query planner, the client, and the protocol, just so he doesn't have to write the column names.
A lot of the points in this article are far too sweeping to be true.
For instance, the statement that using select * will cause too many columns to be read is repeated over and over, but it mostly just isn't true. For instance, if I define a view that does select * from a join of two tables and then do a query that asks for two columns from the view, essentially every optimizer on the planet will push down the two columns and will never retrieve all the columns. The same applies to common table expressions.
Moreover, putting a * in these views or common expressions is actually less error prone than putting in an explicit field list. What you are saying is that the view or expression is passing everything through from a join or filter operation. That's the right thing.
This absolutist sort of generalization that select * needs to be repaired everywhere is just silly.
(At least in Oracle) Rows are held in blocks, and the whole block must be read to retrieve a row within it. If the row entirely fits, there is no difference between selecting a subset of columns versus all of them, as the disk must do the same work. When columns are sharded over several servers, this would not hold true.
Never use SELECT * within the context of an application, as column additions or removals can break the app. It pays to be specific in code.
For interactive use, it's fine, especially when the exact spellings of the target columns is not known. The author might have more of a point on complex views.
It actually appears to go the other way. Explicitly referencing an ambiguous column causes the error while using select * avoids it, at least with duckdb (which is a very standard sort of SQL). This suggests to me that using select * in views is a very good practice.
D create table a (amb integer, a1 integer, a2 integer);
D create table b (amb varchar, b1 integer, b2 integer);
D insert into a values (1,1,1);
D insert into b values ('b',2,2);
D insert into b values ('b',1,3);
D select * from a, b where a.a1 = b.b1;
┌─────┬────┬────┬─────┬────┬────┐
│ amb │ a1 │ a2 │ amb │ b1 │ b2 │
├─────┼────┼────┼─────┼────┼────┤
│ 1 │ 1 │ 1 │ b │ 1 │ 3 │
└─────┴────┴────┴─────┴────┴────┘
D select amb from a, b where a.a1 = b.b1;
Error: Binder Error: Ambiguous reference to column name "amb" (use: "b.amb" or "a.amb")
The database query analyzer is doing you a favor by telling you there's an ambiguous column name and refusing to run, for the same reason a compiler won't allow you to define the same variable name twice.
If they are added they are just another key in the hashmap, no harm. If they are removed, I don't have a solution for that. Typically you don't remove columns of databases like that, but adding them is common.
> Never use SELECT * within the context of an application, as column additions or removals can break the app.
To help with this problem, several databases now support a notion of "invisible columns", including Oracle, MySQL, and MariaDB. Invisible columns are excluded from SELECT *, but otherwise function like normal columns and can be queried explicitly by name.
I suspect that almost any sweeping statement that Oracle doesn't support some feature or other is wrong in the current day.
Oracle can certainly handle columnar formatted data. The in-memory columnar format is just what it says, HCC provides columnar compression and Exadata has done columnar data access forever.
I know little about Oracle specifically, but I do know that it is a huge beast with all kinds of feature that make generalization difficult.
Just keep in mind SELECT * and COUNT(*) are only visually similar.
The first one needs to read all data if used at the top level and the second one needs no actual data - just the overall row count, in whatever way is most optimal to calculate.
I think this belief is a remnant of old versions of MySQL which traversed the entire table counting rows. This is mostly in the past though as they'll typically use the primary key index to count rows or just save the number somewhere (depending on the DBMS)
InnoDB (the most common storage engine in MySQL) uses a clustered index. In other words, the "primary key index" IS the table. Also a COUNT(*) query has to account for MVCC, ensuring that the count represents an accurate total for a specific isolated snapshot.
So this is basically still true today, although MySQL 8 has started to add parallelized reads to improve performance for this situation.
1997... I'd built an extremely basic 'ecommerce' type system, using php/fi and mysql... 3, IIRC (slight chance this might have been mSQL). This was run off a shared hosting system, and... after running for a couple months... it was suspended. The client was ... livid/upset/angry/etc, and I was trying to deal with hosting support. "You're running abusive software which is damaging our server(s), and we've disabled the account until you fix this".
Firstly, the account was completely disabled, so I couldn't log in to even see what was wrong, or to even make a change once I might figure it out. Secondly, they wouldn't tell me what the problem was. Eventually - 2 days later - someone from support said I were typing up their CPUs with my 'shitty SQL', and I should learn that 'select *' was garbage, and my client should hire a 'real developer'.
So... the server is having huge CPU spikes and clogging their network switch (got this over another couple days from another support person). After 8 days, the site/account was back online, and, the real culprit was one of the other 200 accounts on that shared server - they were blasting out spam.
But hey... yeah, have someone grep for "select *" and take immediate action. That it didn't actually stop the problem after a couple hours should have been the first clue.
That was my last direct experience with 'shared hosting' and one of my first "SQL snob" encounters.
In my experience (long years as consultant/admin in the MSP business), it's always the database server or the network that is too slow, and it's never the SQL query or missing indexes. Cough magento cough </s>
25 comments
[ 2.5 ms ] story [ 71.5 ms ] threadIn many cases, I'd like that latter data transfer stage to be smarter. Give me back some smart object which represents a result set, but then lazily only retrieves the information my code actually uses. At the same time, try to predict what information I'll be needing from the result set to avoid server round trips. (If I'm iterating through a result set in order accessing 4 of the 8 columns, you can probably just batch send the data my code uses).
Basically, he wants a bunch of complexity and heuristics to be pushed into the query planner, the client, and the protocol, just so he doesn't have to write the column names.
For instance, the statement that using select * will cause too many columns to be read is repeated over and over, but it mostly just isn't true. For instance, if I define a view that does select * from a join of two tables and then do a query that asks for two columns from the view, essentially every optimizer on the planet will push down the two columns and will never retrieve all the columns. The same applies to common table expressions.
Moreover, putting a * in these views or common expressions is actually less error prone than putting in an explicit field list. What you are saying is that the view or expression is passing everything through from a join or filter operation. That's the right thing.
This absolutist sort of generalization that select * needs to be repaired everywhere is just silly.
Never use SELECT * within the context of an application, as column additions or removals can break the app. It pays to be specific in code.
For interactive use, it's fine, especially when the exact spellings of the target columns is not known. The author might have more of a point on complex views.
Use a dictionary cursor, or some other method to convert whatever is returned into a hashmap like so: https://dev.mysql.com/doc/connector-python/en/connector-pyth...
To help with this problem, several databases now support a notion of "invisible columns", including Oracle, MySQL, and MariaDB. Invisible columns are excluded from SELECT *, but otherwise function like normal columns and can be queried explicitly by name.
Oracle can certainly handle columnar formatted data. The in-memory columnar format is just what it says, HCC provides columnar compression and Exadata has done columnar data access forever.
I know little about Oracle specifically, but I do know that it is a huge beast with all kinds of feature that make generalization difficult.
Never use *, you’ll thank yourself later on.
The first one needs to read all data if used at the top level and the second one needs no actual data - just the overall row count, in whatever way is most optimal to calculate.
So this is basically still true today, although MySQL 8 has started to add parallelized reads to improve performance for this situation.
Firstly, the account was completely disabled, so I couldn't log in to even see what was wrong, or to even make a change once I might figure it out. Secondly, they wouldn't tell me what the problem was. Eventually - 2 days later - someone from support said I were typing up their CPUs with my 'shitty SQL', and I should learn that 'select *' was garbage, and my client should hire a 'real developer'.
So... the server is having huge CPU spikes and clogging their network switch (got this over another couple days from another support person). After 8 days, the site/account was back online, and, the real culprit was one of the other 200 accounts on that shared server - they were blasting out spam.
But hey... yeah, have someone grep for "select *" and take immediate action. That it didn't actually stop the problem after a couple hours should have been the first clue.
That was my last direct experience with 'shared hosting' and one of my first "SQL snob" encounters.