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

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

Solution 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.

Solution 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.

Solution 3:

Another reason why "*" is risky, not only in views but in queries, is that columns can change name or change position in the underlying tables. Using a wildcard means that your view accommodates such changes easily without needing to be changed. But if your application references columns by position in the result set, or if you use a dynamic language that returns result sets keyed by column name, you could experience problems that are hard to debug.

I avoid using the wildcard at all times. That way if a column changes name, I get an error in the view or query immediately, and I know where to fix it. If a column changes position in the underlying table, specifying the order of the columns in the view or query compensates for this.