The other nice benefit is that, when you list the fields you're retrieving, it functions like a mini-reference as you write/modify your query, so you don't have to keep switching to the schema to see if it's called "id" or "row_id", if it's "package_name" or "pkgname"...

To me, "SELECT *" almost feels like using your variables without declaring them first -- not technically wrong in many languages, but still uncomfortable-feeling.

StringBuilder stringBuilder = new StringBuilder();

That is horrendously redundant. Var is handy shorthand and should be used!!

    (stringBuilder :: StringBuilder) <- construct

But what if you're doing something like

    var x = myOtherObjectName.y;

I recall a problem with some Oracle views that were based on 'select <star>' queries. What we didn't realize is that the '*' is evaluated when the view is compiled, and if you later add columns to the underlying tables the view did not automatically include them.

Edit: OK I don't know how to include a literal star in text here.

EDIT: Nope, that wasn't it.

shakes fist at crazy home-grown not-quite-Markdown

  Or you can make a *literal* line by putting two spaces before it.

There's a huge difference between an application executing a sql statement:

    SELECT * FROM accounts;

    CREATE OR REPLACE FUNCTION accounts__list_all()
    RETURNS SETOF accounts
    LANGUAGE SQL AS
    $$
       SELECT * FROM accounts ORDER BY account_no;
    $$;

Not just on the database itself. You also need to ship surplus data to your application and then instantiate objects with surplus fields. All of this takes bandwidth, RAM and time.

I also once saw someone write idiomatic ruby that performed a sum over an ActiveRecord .each. In dev, nobody noticed. In production it pulled a 9 million row table across, iterated over 9 million ruby objects, all to sum up a few hundred kb of actual data that the database could do in a few hundred ms.

The problem is hoping that ORMs will make those icky databases go away. They don't. They can't.

In conclusion: Clickbait for DBAs

I'm not a DBA, but I really like reading articles about common pitfalls and mistakes, to broaden my knowledge base. Something like "'SELECT *' and your ORM might be using it for you" would have been a much more informative title to me, without dancing around the clearly intended use of the word "myth," and I personally would have still read it, without expecting content completely at odds with what I would say is the natural interpretation of the title.

It gets much worse than just single table grab-alls, though. I once worked at a shop where they insisted upon prolifically using "select just about everything from every relational table, comprehensively joined to the n-th level" views as some sort of confused notion about code reuse, such that everything would then select from these master views. The end result was that it was incredibly frustrating trying to resolve performance problems, because literally everything was a performance problem. It was one of those instances where I shook my fists at every naive developer railing about purported premature optimizations.

It doesn't mean anything, because both optimize and premature are in the opinion of the viewer. There is no consensus on this at all, but it has been my experience that when those famous words are uttered, badness is about to occur.

Mortgage-driven development:

http://codemanship.co.uk/parlezuml/blog/?postid=147

Does exactly that. Runs your SQL against the database at compile time to get type information, which it injects into the program AST.

It's not the ORM that causes problems, it's all about the mindset of thinking that you don't have to worry about anything because the ORM will have your back anyway. I use ActiveRecord in Rails every day and there's a lot of things it just does right. It abstracts certain things I wouldn't want to constantly have to worry about and gives me a fine-grained enough layer of control over the queries that I can actually get all the expressiveness and power I need from the records.

Like they say, don't throw the baby with the bath water my friend.

i.e. rails' ActiveRecord allows you to select columns easily in most cases:

    User.where(..) # all fields
    User.where(..).select(:name, :id) # only two of them
    joe.boards.select(:title)  # only one in join
    joe.boards.pluck(:title)   # only column value instead of full objects with one attribute set

You should also be able to setup the select as a default per-class, other than per query.

I suspect, I likely didn't understand your question, could you expand it?

My apologies if it was a dumb question. :)

    .only(… field list…)
    .defer(… fields to fetch on demand…)

It's really a PIA.

Recently, I've learned about http://jooq.org/ — not having checked in detail yet, it might be easier there.

http://www.sqlalchemy.org/

SELECT * can be bad for the following reasons:

- returning more data than you need over a network connection can be a bottleneck. The ORM/java prog written by a programmer that doesn't care may only need a few columns. Tables can be very wide, especially when CLOB/BLOBs are involved

- Stored procedures, functions and triggers that use SELECT * may break horribly when columns are added or removed from tables, views or materialised views

- Table types (collection and record types) will break if any column redefinition occurs

- It shows a lack of understanding of the data by the developer. This may, in turn, cause further issues

Proper schema design and understanding of the data involved is key when working with databases. The existence of NoSQL and the general dumbing down of Computer Science degrees, plus the teaching of ORMs has caused endless problems. The need for NoSQL in the first place was possibly caused by the use of high level programming languages where you didn't need to know the data - you just fetch it all, then piss around with it in ruby/whatever, then fetch it again & again until you're done.

Rant over ;)

The reason I use select * there is that it reduces a maintenance point when it comes to return types and tables. In that case, I am guaranteed to get a return type which matches the table type. If the two are closely linked, then select * makes a lot of sense, and the alternatives aren't going to do better.

This makes schema changes more compatible than trying to change the column everywhere in every query in addition to the higher levels of code.

I would generally agree that an application calling select * from table is a bad thing for the same reasons, but if you are encapsulating your db behind an API, a lot of reasons shift.

As I say, I use select * a lot, but these are in two groups:

1. In UDF's to ensure the proper return type, and

2. Against UDF's where it is the UDF designer's job to maintain the software interface contract.

In those cases, select * works, but it only does so because you have dependency injection and can decide on a case-by-case basis whether to pass the changes up, wrap them in a view, or the like. Most of the time this is a simple choice. The app needs a new column that we are storing and so.... But in a multi-app environment there may be other choices, and that dependency injection makes all the difference in the world.

EDIT: for example, if you have a UDF returning the type of a given table, then you may want to use select * because you have already decided these are to be tied together. Similarly select * from my_udf() is not a bad thing the way that select * from my_table is.

I presume that if you find yourself picking columns out of UDFs, it's a code smell that the UDF needs rethinking.

Thats not too bad, and it does not get the column names mixed up.

(Select * from ... in PostgreSQL is actually really usefun in anything object-relational because you get objects of a named type back btw.)

The columns being renamed on views bit us big time. We had a large ERP package that we'd built numerous custom views on top of. Whenever we applied an update to the ERP package, it'd add columns and all our views would be borked.

Is select count(1) from mytable faster than select count(*) from mytable?

  SELECT
      *
  FROM table1
  JOIN table2
      ON table2.field = table1.field

Therefore always use: SELECT table1.* FROM table1

Of course if you have that sort of system, select * is the least of your worries.

Perhaps an example of table checking_account_register hmm lets add a new feature, after a paper check is cancelled we'll scan it into an image file and stick it in the database. Suddenly you get giant TIFF for each row returned, surprise! Of course a better spot Might be a separate scan table linking checks to an image of the check (perhaps multiple images, multiple scan attempts, multiple sides of the check, and all that), but for the sake of argument, etc.

I think probably more databases get killed by processing load via no WHERE or LIMIT clause than from using a * as a column list... probably. With a close second of gathering way too much data and weeding it out in a HAVING.