SELECT * - pros /cons

Question

I always hear from SQL specialists that it is not efficient to use the '*' sign in SELECT statement and it is better to list all the field names instead.

But I don't find it efficient for me personally when it comes to adding new fields to a table and then updating all the stored procedures accordingly.

So what are the pros and cons in using '*' ?
Thanks.

possible duplicate of [Can select \* usage ever be justified?](http://stackoverflow.com/questions/3635781/can-select-usage-ever-be-justified) — John Doyle, Jul 14 '13 at 10:21

score 13 · Accepted Answer · edited May 23 '17 at 12:22

13

In general, the use of SELECT * is not a good idea.

Pros:

When you add/remove columns, you don't have to make changes where you did use SELECT *
It is shorter to write
Also see the answers to: Can select * usage ever be justified?

Cons:

You are returning more data than you need. Say you add a VARBINARY column that contains 200k per row. You only need this data in one place for a single record - using SELECT * you can end up returning 2MB per 10 rows that you don't need
Explicit about what data is used
Specifying columns means you get an error when a column is removed
The query processor has to do some more work - figuring out what columns exist on the table (thanks @vinodadhikary)
You can find where a column is used more easily
You get all columns in joins if you use SELECT *
You can't use ordinal referencing (though using ordinal references for columns is bad practice in itself)
Also see the answers to: What is the reason not to use select *?

edited May 23 '17 at 12:22

Community

1
1

answered Jul 14 '13 at 08:22

Oded

489,969
99
883
1,009

3

To add to the cons, the query processor has to figure out what columns exists in the particular table first. – vee Jul 14 '13 at 08:24
1

@vinodadhikary - Why would that be more work than validating an explicit column list containing all column names? I'd imagine it might be slightly less work in fact as it just needs to retrieve all column metadata not validate each one individually. – Martin Smith Jul 14 '13 at 09:34
@MartinSmith, The wild character `*` has to be expanded before all fields are validated. So if the `select` contains an `*` that gets added to the field_list. The optimization is minimal as far as performance is concerned but that extra function call is saved. I couldn't really find the Oracle's version of MySQL source (and I didn't really bother digging for it), but found `twitter`'s version in github. Have a look at `https://github.com/twitter/mysql/blob/master/`, `sql/sql_select.cc` `JOIN::prepare` between lines 498 and 554 and `setup_wild` at line 7846 in `sql/sql_base.cc`. – vee Jul 14 '13 at 19:42

score 5 · Answer 2 · answered Jul 14 '13 at 08:24

Pros:

when you really need all the columns, it's shorter to write select *

Cons:

most of the time, you don't need all the columns, but only some of them. It's more efficient to only retrieve what you want
you have no guarantee of the order of the retrieved columns (or at least, the order is not obvious from the query), which forbids accessing columns by index (only by name). But the names are also far from obvious
when joining multiple tables having potentially columns with the same name, you can define aliases for these columns

SELECT * - pros /cons

2 Answers2