The asterisk (*) has a special meaning in SELECT statements:
- When specified without a qualifier, it is resolved as a reference to all columns in all tables or views specified in the FROM clause. This example retrieves all book information stored in the Shippers table:
USE Northwind GO SELECT * FROM Shippers ORDER BY CompanyName GO
- When qualified with a table or view name, it is resolved as a reference to all the columns in the table or view. This example uses the asterisk to reference all the columns in the Shippers table:
USE Northwind GO SELECT Orders.OrderID, Shippers.* FROM Shippers JOIN Orders ON (Shippers.ShipperID = Orders.ShipVia) ORDER BY Orders.OrderID GO
When * is used, the order of the columns in the result set is the same as the order in which they were specified in the CREATE TABLE, ALTER TABLE, or CREATE VIEW statements.
Because SELECT * finds all columns currently in a table, changes in the structure of a table (adding, removing, or renaming columns) are automatically reflected each time a SELECT * statement is executed.
If a SELECT is used in an application or script that has logic dependent on the number of columns in the result set, it is better to specify all the columns in the select list rather than specify an asterisk. If columns are later added to the table or views referenced by the SELECT statement, the application is shielded from the change if the columns were listed individually. If * was specified, the new columns become a part of the result set and may affect the logic of the application or script.
This example retrieves all columns in the publishers table and displays them in the order in which they were defined when the publishers table was created:
USE Northwind GO SELECT * FROM [Order Details] ORDER BY OrderID ASC GO
To get exactly the same results, explicitly list all the column names in the table, in order, after the SELECT statement:
USE Northwind GO SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details] ORDER BY OrderID ASC GO
Note To find out the column names for a table, use sp_help, use SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = table, or use SELECT TOP 0 * FROM table.