What are the pros and cons of using `select table_name.*`? [closed]

Question

I've heard that this is the wrong way to write queries in general but I wonder something people say to new users of SQL.

Is it really bad form or just plain lazy?

So, not to solicit too much opinion on this, what are some good reasons for it's use in queries besides not having to type out every field name. Also, do these reasons outweigh the harm in it's use?

Answer 1

When your program knows about the structure of your table at compile-time or through configuration, using select * is not a good idea: any change in the structure of your table could break the structure of the results coming back from the query, ultimately causing run-time errors.

However, there are cases when * is indispensable. Specifically, if your program "learns" dynamically of the structure of your tables by reading metadata coming back from a query, using the "all columns" request lets your program pick up changes to your tables dynamically.

It goes without saying that using * for ad-hoc queries in your favorite flavor of SQL Studio/SQLPlus/etc. is very common and convenient.