Why is using '*' to build a view bad?

Question

Why is using '*' to build a view bad ?

Suppose that you have a complex join and all fields may be used somewhere.

Then you just have to chose fields needed.

SELECT field1, field2 FROM aview WHERE ... 

The view "aview" could be SELECT table1.*, table2.* ... FROM table1 INNER JOIN table2 ...

We have a problem if 2 fields have the same name in table1 and table2.

Is this only the reason why using '*' in a view is bad?

With '*', you may use the view in a different context because the information is there.

What am I missing ?

Regards

Answer 1

I don't think there's much in software that is "just bad", but there's plenty of stuff that is misused in bad ways :-)

The example you give is a reason why * might not give you what you expect, and I think there are others. For example, if the underlying tables change, maybe columns are added or removed, a view that uses * will continue to be valid, but might break any applications that use it. If your view had named the columns explicitly then there was more chance that someone would spot the problem when making the schema change.

On the other hand, you might actually want your view to blithely accept all changes to the underlying tables, in which case a * would be just what you want.

Update: I don't know if the OP had a specific database vendor in mind, but it is now clear that my last remark does not hold true for all types. I am indebted to user12861 and Jonny Leeds for pointing this out, and sorry it's taken over 6 years for me to edit my answer.

Answer 2

Although many of the comments here are very good and reference one common problem of using wildcards in queries, such as causing errors or different results if the underlying tables change, another issue that hasn't been covered is optimization. A query that pulls every column of a table tends to not be quite as efficient as a query that pulls only those columns you actually need. Granted, there are those times when you need every column and it's a major PIA having to reference them all, especially in a large table, but if you only need a subset, why bog down your query with more columns than you need.