Contents Index The SELECT list: specifying columns Selecting specific columns from a table

ASA SQL User's Guide
  Queries: Selecting Data from a Table
    The SELECT list: specifying columns

Selecting all columns from a table


The asterisk (*) has a special meaning in SELECT statements. It stands for all the column names in all the tables specified in the FROM clause. You can use it to save typing 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 department table. No WHERE clause is included; and so this statement retrieves every row in the table:

SELECT *
FROM department

The results look like this:

dept_id dept_name dept_head_id
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 dept_id, dept_name, dept_head_id
FROM department

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

SELECT department.*
FROM department

Contents Index The SELECT list: specifying columns Selecting specific columns from a table