ARCHIVE / Why should I avoid using SELECT * in a SQL query?

The * symbol in SQL, as in some other computing languages, acts as a wildcard or shorthand for "everything".  SELECT COUNT(*) FROM a_table, for example, requests a count of every row from a_table.  Knowing that the contents of a table need to be counted and not directly accessed, a relational database management system can often optimize such a request.  However, SELECT * FROM a_table requests all columns from all rows of a_table.  In doing so, the database management system must effectively copy all of the data from the table in order to return the results of the query.  Because of the need to copy the entire data set, such a query is less efficient than simply accessing the data in a raw form as a file unless the database management system is storing most of the data in active memory.  Yet, even when requesting only a single row, such as SELECT * FROM a_table WHERE some_column = some_unique_value, it is still generally advisable not to use SELECT * because the definition of the table may change over time.  For example, a table may have a column added, removed, renamed or repositioned relative to other columns.  Such changes can result in SQL queries returning data that are never used, attempting to implicitly access data that do not exist, implicitly relying upon a column with a given name or relying upon the relative ordering of the table's columns.  While explicitly indicating only those columns one needs does not prevent problems when a column is removed or renamed, it does prevent problems when a column is added or repositioned and makes it easier to find a problematic query when one is able to search on the text of the column name.

last updated 2007.10.28

RELATED TOPICS

 

Terms of Use · Privacy Policy ©2021