The asterisk (*) selects all the column names in all the tables specified by the from clause.
Use the asterisk to save typing time and errors when you want to see all the columns in a table. When you use the asterisk in a query, data is retrieved in create table order.
The syntax for selecting all the columns in a table is:
select * from table_list
The following statement retrieves all columns in the publishers table and displays them in create table order. This statement retrieves all rows since it contains no where clause:
select * from publishers
The results look like this:
pub_id pub_name city state ----- -------------- --------- ----- 0736 New Age Books Boston WA 0877 Binnet & Hardley Washington DC 1389 Algodata Infosystems Berkeley CA (3 rows affected)
If you listed all the column names in the table in order after the select keyword, you would get exactly the same results:
select pub_id, pub_name, city, state from publishers
You can also use “*” more than once in a query:
select *, * from publishers
This query displays each column name and each piece of column data twice. Like a column name, you can qualify an asterisk with a table name. For example:
select publishers.* from publishers
However, because select * finds all the columns currently in a table, changes in the structure of a table such as adding, removing, or renaming columns automatically modify the results of select *. Listing columns individually gives you more precise control over the results.