There are some anti-patterns one should avoid when writing SQL queries. Sometimes these may seem like a shortcut, but in reality this can lead to bugs, problems, and brittle applications.
It’s always better to use the explicit column list in the SELECT query than a * (star) wildcard. It not only improves the performance but also makes your code more explicit. It also helps you create maintainable code, which will not break when you add/remove columns from your table, especially if you have views that refer to the original table.
# SELECT *
Doing a SELECT * may seem like a time-saver, but it’s actually setting you up for problems in the long run, specially when database schema changes.
# Breaks Views While Adding New Columns to a Table
When you use SELECT * in views, then you create subtle bugs if a new column has been added ro an old one is removed from the table. Why? Because your view might break, or start returning an incorrect result.
# Dependency on Order of Columns on ResultSet
When you use the SELECT * query in your application and have any dependency on order of column, which you should not, the ordering of the result set will change if you add a new column or change the order of columns.
# Conflicts in a JOIN Query
When you use SELECT * in JOIN query, you can introduce complications when multiple tables have columns with the same name e.g. status, active, name, etc.
# More Application Memory
Due to this increase in data, your application may require more memory just to hold unnecessary data that it will not be using
# Increased Network Traffic
SELECT * obviously returns more data than required to the client, which, in turn, will use more network bandwidth.
# Unnecessary I/O (Input Output)
By using SELECT *, you can be returning unnecessary data that will just be ignored, but fetching that data is not free of cost.
# COUNT(*)
One should default to using COUNT clauses with column names, such as COUNT(id), since this will count values which are non-NULL.
If NULL is explicitly wanted then one can choose for COUNT(1) or COUNT(*).
There is a negligible performance difference, at least in PostgreSQL between 1 and * , so use at your discretion.