Why You Should NOT Use SELECT *

Summary

The video script emphasizes the pitfalls of using 'SELECT *' in SQL queries, citing inefficiency, potential index misuse, vulnerability to table changes, and maintenance difficulties. It advocates for specifying columns to improve performance and maintainability, with the exception of ad-hoc queries for initial data exploration. The speaker also promotes SQL Cheat Sheets for mastering SQL syntax.

Takeaways

• 🚫 Avoid using 'SELECT *' in production code as it can lead to inefficiencies and maintenance issues.
• ⚙️ Writing 'SELECT *' might seem convenient, but it can waste resources by retrieving more data than necessary.
• 🔍 Indexes may not be utilized effectively if all columns are selected, potentially slowing down query performance.
• 🛠 Be cautious, as 'SELECT *' can break your application if the table schema changes, like adding or removing columns.
• 📝 'SELECT *' queries are hard to maintain and understand, as they do not explicitly list the needed columns.
• 👀 For quick checks or initial data exploration, 'SELECT *' can be useful, but not for feature development.
• 💡 Using 'SELECT *' in ad-hoc queries helps in understanding the structure and content of a new database.
• 📈 When working with new datasets or databases, 'SELECT *' can assist in identifying column names and data types.
• ❌ Refrain from using 'SELECT *' in production environments to prevent unexpected changes and performance issues.
• 🔗 For a better grasp on SQL syntax and commands, consider using cheat sheets to improve efficiency and accuracy.

Q & A

Why is it generally advised not to use 'SELECT *' in SQL queries?

Using 'SELECT *' can be inefficient because it retrieves all columns from a table, even if only a few are needed, leading to unnecessary data transfer and potential waste of resources.

How can 'SELECT *' affect the use of indexes in a database?

Indexes are optimized for specific query patterns. If a query uses 'SELECT *', the database might decide not to use an index that could have improved performance for a more targeted column selection.

What is the potential risk of using 'SELECT *' when the table schema changes?

If a table's schema changes, such as adding or removing columns, a 'SELECT *' query will automatically include these changes without any modifications to the query itself, which can lead to unexpected results or performance issues.

How does using 'SELECT *' impact the maintainability of code?

'SELECT *' makes it difficult to understand which columns are being returned, complicating the process of working with the data in application code or reporting tools, and thus making the code harder to maintain.

In what scenario is it acceptable to use 'SELECT *' according to the script?

Using 'SELECT *' is acceptable for quick, ad-hoc queries where the goal is to explore the contents of a table, such as when examining a new database or loading a sample dataset.

What is the purpose of an index in a database?

An index in a database is an object that helps improve the performance of queries by allowing the database to quickly look up data without scanning every row in a table.

Why might a database choose not to use an index when a 'SELECT *' query is executed?

A database may opt not to use an index with a 'SELECT *' query because the query does not specify a pattern that the index is designed to optimize, leading to a full table scan instead.

What is the impact of retrieving more data than necessary on client-side performance?

Retrieving more data than necessary can slow down the client-side performance as the client has to process and possibly display a larger amount of data, which can be inefficient and wasteful.

How can a 'SELECT *' query affect the results or performance of a query when the table schema is altered?

A 'SELECT *' query can cause the results to change or the query to slow down if the table schema is altered, such as when a column is added or removed, because the query will automatically reflect these changes without any manual intervention.

What is the recommendation for using 'SELECT *' in feature development or production code?

It is recommended not to use 'SELECT *' in feature development or production code due to its inefficiency, potential for performance issues, and difficulty in maintaining the code.

What resource is mentioned in the script for those who want a quick reference guide for SQL commands and features?

The script mentions SQL Cheat Sheets as a resource for those who need a quick reference guide for SQL commands and features to avoid forgetting the syntax.