The asterisk (*) has a special meaning in SELECT statements. It represents all column names in all tables specified in the FROM clause. This is the syntax for selecting all columns in a table:
SELECT *
FROM table_name
Because SELECT * finds all columns currently in a table, changes in the structure of a table (adding, removing, or renaming columns) automatically modify the results of a SELECT * statement. Listing columns individually allows more precise control over the results. Whenever * is used, the columns in the results are displayed in the order in which they were created by the CREATE TABLE statement.
It is usually preferable to explicitly name the columns in the select list. Because an application often relies on knowing certain columns will be returned, using SELECT * might "break" an application if a new column is added to the table.
The following statement retrieves all columns in the publishers table and displays them in the order in which they were defined when the publishers table was created. Because no WHERE clause is included, this statement also retrieves every row.
SELECT * FROM publishers
The results look like this:
pub_id pub_name city state country ------ ------------------------------------------------------- 0736 New Moon Books Boston MA USA 0877 Binnet & Hardley Washington DC USA 1389 Algodata Infosystems Berkeley CA USA 1622 Five Lakes Publishing Chicago IL USA 1756 Ramona Publishers Dallas TX USA 9901 GGG&G München (null)Germany 9952 Scootney Books New York NY USA 9999 Lucerne Publishing Paris (null)France (8 row(s) affected)
You get exactly the same results by listing all the column names in the table in order after the SELECT statement:
SELECT pub_id, pub_name, city, state FROM publishers