Choosing all columns using select *

The asterisk (*) selects all the column names in all the tables specified by the from clause. Use it to save typing time and errors when you want to see all the columns in a table. * retrieves the data 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.