Selecting all columns from a table

The asterisk (*) has a special meaning in SELECT statements. It represents all the column names in all the tables specified in the FROM clause. You can use it to save entering time and errors when you want to see all the columns in a table.

When you use SELECT *, the columns are returned in the order in which they were defined when the table was created.

The syntax for selecting all the columns in a table is:

SELECT *
FROM table-expression;

SELECT * finds all the columns currently in a table, so that changes in the structure of a table such as adding, removing, or renaming columns automatically modify the results of SELECT *. Listing the columns individually gives you more precise control over the results.

Example

The following statement retrieves all columns in the Departments table. No WHERE clause is included; therefore, this statement retrieves every row in the table:

SELECT *
FROM Departments;

The results look like this:

DepartmentID	DepartmentName	DepartmentHeadID
100	R & D	501
200	Sales	902
300	Finance	1293
400	Marketing	1576
...	...	...

You get exactly the same results by listing all the column names in the table in order after the SELECT keyword:

SELECT DepartmentID, DepartmentName, DepartmentHeadID
FROM Departments;

Like a column name, "*" can be qualified with a table name, as in the following query:

SELECT Departments.*
FROM Departments;

Discuss this page in DocCommentXchange.