Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Yesterday I was discussing with a "hobby" programmer (I myself am a professional programmer). We came across some of his work, and he said he always queries all columns in his database (even on/in production server/code).

I tried to convince him not to do so, but wasn't so successful yet. In my opinion a programmer should only query what is actually needed for the sake of "prettiness", efficiency and traffic. Am I mistaken with my view?

share|improve this question
1  
I'd say its cos what if the table contents change? adding/removing columns? your still selecting *.. so you're gonna be missing things or pulling back more data than you need. –  JF it Apr 3 at 13:15
2  
@JFit That's part of it, but far from the whole story. –  jwenting Apr 3 at 14:37
7  
Good reasons at SO - stackoverflow.com/questions/3180375/select-vs-select-column –  Bratch Apr 3 at 15:40
6  
And good reasons here, Why is select * considered harmful? –  Ellie Kesselman Apr 3 at 16:22
    
@gnat can a question really be considered a duplicate of a closed question? (ie because the closed one wasn't really suitable in the first place) –  gbjbaanb Apr 4 at 7:30

10 Answers 10

up vote 63 down vote accepted

Think about what you're getting back, and how you bind those to variables in your code.

Now think what happens when someone updates the table schema to add (or remove) a column, even one you're not directly using.

Using select * when you're typing queries by hand is fine, not when you're writing queries for code.

share|improve this answer
8  
Performance, network load, etc. etc. are far more important than convenience of getting the columns back in the order and with the name you want. –  jwenting Apr 3 at 14:38
17  
@jwenting really? performance matters more than correctness? Anyway, I don't see that "select *" performs better than selecting only the columns you want. –  gbjbaanb Apr 3 at 14:49
8  
@Bratch, in real life production environments, you might have hundreds of applications using the same tables and there's no possible way all those applications can be maintained properly. You are correct in sentiment, but practically, the argument fails just due to the realities of working in copmanies. Schema changes to active tables happens all the time. –  user1068 Apr 3 at 15:54
13  
I don't understand the point in this answer. If you add a column to a table, both the SELECT * and the SELECT [Columns] will work, the only difference is that if the code needs to bind to the new column, the SELECT [Columns] will need to be modified whereas the SELECT * will not. If a column is removed from a table, the SELECT * will break at the point of binding, whereas the SELECT [Columns] will break when the query is executed. It seems to me that the SELECT * is the more flexible option as any changes to the table would only require changes to the binding. Am I missing something? –  TallGuy Apr 3 at 19:26
10  
@gbjbaanb then access the columns by name. Anything else would be obviously stupid unless you specified the column order in the query. –  immibis Apr 3 at 22:14

Schema Changes

  • Fetch by order --- If the code is fetching column # as the way to get the data, a change in the schema will cause the column numbers to readjust. This will mess up the application and bad things will happen.
  • Fetch by name --- If the code is fetching column by name such as foo, and another table in the query adds a column foo, the way this is handled may cause problems when trying to get the right foo column.

Either way, a schema change can cause problems with the extraction of the data.

Further consider if a column that was being used is removed from the table. The select * from ... still works but errors out when trying to pull the data out of the result set. If the column is specified in the query, the query will error out instead giving a clear indiciation as to what and where the problem is.

Data overhead

Some columns can have a significant amount of data associated with them. Selecting back * will pull all the data. Yep, here's that varchar(4096) thats on 1000 rows that you've selected back giving you an additional possible 4 megabytes of data that you're not needing, but is sent across the wire anyways.

Related to the schema change, that varchar might not exist there when you first created the table, but now its there.

Failure to convey intent

When you select back * and get 20 columns but only need 2 of them, you are not conveying the intent of the code. When looking at the query that does a select * one doesn't know what the important parts of it are. Can I change the query to use this other plan instead to make it faster by not including these columns? I don't know because the intent of what the query returns isn't clear.


Lets look at some SQL fiddles that explore those schema changes a bit more.

First, the initial database: http://sqlfiddle.com/#!2/a67dd/1

DDL:

create table one (oneid int, data int, twoid int);
create table two (twoid int, other int);

insert into one values (1, 42, 2);
insert into two values (2, 43);

SQL:

select * from one join two on (one.twoid = two.twoid);

And the columns you get back are oneid=1, data=42, twoid=2, and other=43.

Now, what happens if I add a column to table one? http://sqlfiddle.com/#!2/cd0b0/1

alter table one add column other text;

update one set other = 'foo';

And my results from the same query as before are oneid=1, data=42, twoid=2, and other=foo.

A change in one of the tables disrupts the values of a select * and suddenly your binding of 'other' to an int is going to throw an error and you don't know why.

If instead your SQL statement was

select 
    one.oneid, one.data, two.twoid, two.other
from one join two on (one.twoid = two.twoid);

The change to table one would not have disrupted your data. That query runs the same before the change and after the change.


Indexing

When you do a select * from you are pulling all the rows form all the tables that match the conditions. Even tables you really don't care about. While this means more data is transferred there's another performance issue lurking further down the stack.

Indexes. (related on SO: How to use index in select statement?)

If you are pulling back lots of columns the database plan optimizer may disregard using an index because you are still going to need to fetch all those columns anyways and it would take more time to use the index and then fetch all of the columns in the query than it would be just to do a complete table scan.

If you are just selecting the, say, last name of a user (which you do a lot and so have an index on it), the database can do an index only scan (postgres wiki index only scan, mysql full table scan vs full index scan, Index-Only Scan: Avoiding Table Access).

There is quite a bit of optimizations about reading only from indexes if possible. The information can be pulled in faster on each index page because you're pulling less of it also - you're not pulling in all those other columns for the select *. It is possible for an index only scan to return results on the order of 100x faster (source: Select * is bad).

This isn't saying that a full index scan is great, its still a full scan - but its better than a full table scan. Once you start chasing down all the ways that that select * hurts performance you keep finding new ones.

Related reading

share|improve this answer
2  
@Tonny I'd agree - but when I answered (first) I never thought this question would generate quite so much discussion and commentary! Its obvious to query only for named columns, isn't it?! –  gbjbaanb Apr 4 at 7:28
3  
Breaking everything by adding a column is also a good reason why code should always access columns in a datareader by name not by hard-coded ordinal... –  Julia Hayward Apr 4 at 8:51
1  
@gbjbaanb It is to me. But a lot of people come to write SQL queries without a formal background/training. To them is may not be obvious. –  Tonny Apr 4 at 9:01
1  
@Aaronaught I've updated it with the additional bit on the indexing issues. Are there any other points that I should bring up for the wrongness of select *? –  MichaelT Apr 7 at 0:59
3  
Wow, the accepted answer was so poor at actually explaining anything that I down-voted it. Amazed that this isn't the accepted answer. +1. –  Ben Lee Apr 8 at 16:25

Another concern: if it's a JOIN query and you're retrieving query results into an associative array (as could be the case in PHP), it's bug-prone.

The thing is that

  1. if table foo has columns id and name
  2. if table bar has columns id and address,
  3. and in your code you are using SELECT * FROM foo JOIN bar ON foo.id = bar.id

guess what happens when someone adds a column name to the bar table.

The code will suddenly stop working properly, because now the name column appears in the results twice and if you're storing the results into an array, data from second name (bar.name) will overwrite the first name (foo.name)!

It's quite a nasty bug because it's very non-obvious. It can take a while to figure out, and there's no way the person adding another column to the table could have anticipated such undesirable side effect.

(True story).

So, don't use *, be in control of what columns you are retrieving and use aliases where appropriate.

share|improve this answer
    
okay in this case (which i consider sort of rare) it could be a major issue. But you could still avoid(and most people probably will) it by querying with the wildcard and just add an alias for the identical column names. –  the baconing Apr 3 at 12:36
4  
In theory, but if you use a wildcard for convenience you rely on it to automatically give you all columns in existence and never bother to update the query as the tables grow. If you are specifying each and every column, you are forced to go to the query to add another one to your SELECT clause and this is when you hopefully spot the name is not unique. BTW I don't think it's so rare in systems with large databases. As I said, I once spent a couple of hours hunting this bug in a big mudball of PHP code. And I found another case just now: stackoverflow.com/q/17715049/168719 –  Konrad Morawski Apr 3 at 12:40
3  
I spend an hour last week trying to get this through a a consultants head. He is supposed to be a SQL guru... Sigh... –  Tonny Apr 3 at 22:30

Querying every column might be perfectly legitimate, in many cases.

Always querying every column isn't.

It's more work for your database engine, which has to go off and rummage around its internal metadata to work out which columns it needs to deal with before it can get on with the real business of actually getting the data and sending it back to you. OK, it's not the biggest overhead in the world, but system catalogs can be an appreciable bottleneck.

It's more work for your network, because you're pulling back any number of fields when you might only want one or two of them. If somebody [else] goes and adds a couple of dozen extra fields, all of which contains big chunks of text, you're throughput suddenly goes through the floor - for no readily apparent reason. This is made worse if your "where" clause isn't particularly good and you're pulling back lots of rows as well - that's potentially a lot of data tromping its way across the network to you (i.e. it's going to be slow).

It's more work for your application, having to pull back and store all of this extra data that it quite probably doesn't care about.

You run the risk of columns changing their order. OK, you shouldn't have to worry about this (and you won't if you select only the columns you need) but, if you go get them all at once and somebody [else] decides to rearrange the column order within the table, that carefully crafted, CSV export that you give to accounts down the hall suddenly goes all to pot - again, for no readily apparent reason.

BTW, I've said "someone [else]" a couple of times, above. Remember that databases are inherently multi-user; you may not have the control over them that you think you do.

share|improve this answer
2  
I would think that always querying every column may be legitimate for things like schema-agnostic table viewing facilities. Not a terribly common situation, but in the context of internal-use-only tools such things can be handy. –  supercat Apr 3 at 15:11
1  
@supercat That is just about the ONLY valid use-case for a "SELECT *" that I can think of. And even then I would prefer to limit the query to "SELECT TOP 10 *" (in MS SQL) or add "LIMIT 10" (mySQL) or add "WHERE ROWNUM <=10" (Oracle). Usually in that case it's more about "what columns are there and some sample data" than the complete content. –  Tonny Apr 3 at 22:39
    
@Tonny: SQL Server changed their default scripts to add the TOP limitation; I'm not sure how important that is if code reads as many as it cares to display and then disposes the query. I think query responses are processed somewhat lazily, though I don't know the details. In any case, I think that rather than saying it "isn't legitimate", it would be better to say "...is legitimate in far fewer"; basically, I'd summarize the legitimate cases as those where the user would have a better idea what's meaningful than the programmer. –  supercat Apr 3 at 22:44
    
@supercat I can agree to that. And I really like the way you put it in your last sentence. I have to remember that one. –  Tonny Apr 3 at 22:49

The short answer is: it depends on what database they use. Relational databases are optimized for extracting the data you need in a fast, reliable and atomic way. On large datasets and complex queries it's much faster and probablly safer than SELECTing * and do the equivalent of joins on the 'code' side. Key-value stores might not have such functionalities implemented, or might not be mature enough to use in production.

That said, you can still populate whatever data structure you're using with SELECT * and work out the rest in code but you'll find performance bottlenecks if you want to scale.

The closest comparison is sorting data: you can use quicksort or bubblesort and the result will be correct. But won't be optimized, and definitely will have issues when you introduce concurrency and need to sort atomically.

Of course, it's cheaper to add RAM and CPUs than investing in a programmer that can do SQL queries and has even a vague understanding of what a JOIN is.

share|improve this answer
    
Learn SQL! It isn't that hard. It is the "native" language of databases far and wide. It's powerful. It's elegant. It has stood the test of time. And there's no way you're going to write a join on the "code" side that's more efficient than the join in the database, unless you are really inept at doing SQL joins. Consider that in order to do a "code join," you have to pull all the data from both tables in even a simple 2-table join. Or are you pulling index stats and using those to decide which table data to pull before you join? Didn't think so... Learn to use the database correctly, people. –  Craig Apr 5 at 13:23
    
@Craig: SQL is common in relational databases far and wide. That's far from the only type of DB, though...and there's a reason more modern database approaches are often called NoSQL. :P No one i know would go calling SQL "elegant" without a heavy dose of irony. It just sucks less than many of the alternatives, as far as relational databases are concerned. –  cHao Apr 6 at 8:27
    
@cHao I've been very aware of the various other types of databases out there for decades. The Pick "nosql" database has been around forever. "NoSQL" isn't even remotely a new concept. ORM's have also been around forever, and they've always been slow. Slow != Good. As for elegance (LINQ?), you can't convince me this is reasonable or elegant for a where clause: Customer customer = this._db.Customers.Where( “it.ID = @ID”, new ObjectParameter( “ID”, id ) ).First(); See Time to Take Offense on page 2. –  Craig Apr 6 at 18:38
    
@Craig: Don't even get me started on ORM. Nearly every system out there does it horribly, and the abstraction leaks all over the place. That's because relational DB records aren't objects -- at best, they're the serializable guts of part of an object. But as for LINQ, you really want to go there? The SQLish equivalent is something like var cmd = db.CreateCommand(); cmd.CommandText = "SELECT TOP 1 * FROM Customers WHERE ID = @ID"; cmd.Parameters.AddWithValue("@ID", id); var result = cmd.ExecuteReader();....and then proceed to create a Customer from each row. LINQ beats the pants off that. –  cHao Apr 6 at 19:06
    
@Craig: Granted, it's not as elegant as it could be. But it'll never be as elegant as i'd like til it can convert .net code to SQL. :) At which point you could say var customer = _db.Customers.Where(it => it.id == id).First();. –  cHao Apr 6 at 19:16

IMO, its about being explicit vs implicit. When I write code, I want it to work because I made it work, not just because all of the parts just happen to be there. If you query all records and your code works, then you'll have the tendency to move on. Later on if something changes and now your code doesn't work, its a royal pain to debug lots of queries and functions looking for a value that should be there and the only values reference are *.

Also in an N-tiered approach, its still best to isolate database schema disruptions to the data tier. If your data tier is passing * to the business logic and most likely on the the presentation tier, you are expanding your debugging scope exponentially.

share|improve this answer
3  
This is probably one of the most important reasons here, and it's got just a tiny fraction of the votes. The maintainability of a codebase littered with select * is much worse! –  Eamon Nerbonne Apr 5 at 9:39

because if the table gets new columns then you get all those even when you don't need them. with varchars this can become a lot of extra data that needs to travel from the DB

some DB optimizations may also extract the non fixed length records to a separate file to speed up access to the fixed length parts, using select* defeats the purpose of that

share|improve this answer

Apart from overhead, something you want to avoid in the first place, I would say that as an programmer you don't depend on column order defined by the database administrator. You select each column even if you need them all.

share|improve this answer
3  
Agree, though I'd also recommend pulling out values from a result set by column name in any case. –  Rory Hunter Apr 3 at 14:05
    
Seconded, carried. Use the column names, do not depend on the column order. The column order is a brittle dependency. The names should have (you hope) been derived from some actual design effort, or you explicitly alias composite columns or computations or conflicting column names in your query, and reference the explicit alias that you specified. But relying on the order is pretty much just duct tape and prayer... –  Craig Apr 5 at 13:18

I don't see any reason why you shouldn't use for the purpose it's build - retrieve all the columns from a database. I see three cases:

  1. A column is added in the database and you want it in code also. a) With * will fail with a proper message. b) Without * will work, but won't do what you expect which is pretty bad.

  2. A column is added in database and you do not want it in code. a) With * will fail; this means that * does no longer applies since it's semantics means "retrieve all". b) Without * will work.

  3. A column is removed Code will fail either way.

Now the most common case is case 1 (since you used * which means all you most probably want all); without * you can have code that works fine but doesn't do what expected which is much, much worst that code that fails with a proper error message.

I'm not taking into consideration the code which retrieves the column data based on column index which is error-prone in my opinion. It's much more logic to retrieve it based on column name.

share|improve this answer
    
Your premise is incorrect. Select * was intended more as a convenience for ad-hoc querying, not for application development purposes. Or for use in statistical constructs like select count(*) which lets the query engine decide whether to use an index, which index to use and so on and you aren't returning any actual column data. Or for use in clauses like where exists( select * from other_table where ... ), which again is an invitation to the query engine to pick the most efficient path on its own and the subquery is only used to constrain results from the main query. Etc. –  Craig Apr 10 at 3:14
    
@Craig I believe every book/tutorial on SQL says that select * has the semantics of retrieving all the columns; if your application really needs this, I don't see any reasons why not use it. Can you point to some reference (Oracle, IBM, Microsoft etc.) that mention the purpose for which select * was build is not to retrieve all columns? –  m3th0dman Apr 10 at 6:18
    
Well, of course select * exists to retrieve all the columns... as a convenience feature, for ad-hoc querying, not because it's a great idea in production software. The reasons are already covered pretty well in the answers on this page, which is why I didn't create my own detailed answer: •) Performance problems, repeatedly marshalling data over the network that you never use, •) problems with column aliasing, •) query plan optimization failures (failure to use indexes in some cases), •) inefficient server I/O in cases where limited select could have solely used indexes, etc. –  Craig Apr 10 at 14:44
    
Maybe there's an edge case here or there that justifies the use of select * in an actual production application, but the nature of an edge case is that it is not the common case. :-) –  Craig Apr 10 at 15:01
    
@Craig The reasons are against retrieving all columns from a database not against using select *; what I was saying if you really need all the columns, I see no reason why you shouldn't use select *; although few there must be scenarios where all the columns are needed. –  m3th0dman Apr 10 at 16:45

Think of it this way... if you query all columns from a table that has just a few small string or numeric fields, that total 100k of data. Bad practice, but it will perform. Now add a single field that holds, say, an image or a 10mb word document. now your fast performing query immediately and mysteriously start performing poorly, just because a field was added to the table... you may not need that huge data element, but because you've done Select * from Table you get it anyway.

share|improve this answer
6  
this seems to merely repeat point already made few hours ago in a first answer and in couple of other answers –  gnat Apr 3 at 19:02

protected by Thomas Owens Apr 4 at 10:27

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.