Retrieving All Columns with the SELECT Clause

When an * character is used in the projection-list portion of a SELECT statement, the system retrieves all columns from the tables appearing in the FROM clause. For example, suppose we need to display all columns in the SUPPLIER table. The SELECT statement below would accomplish this objective:

SELECT *
FROM supplier

The result for this query is shown in the table below:

sno sname status city
S1 SMITH 20 LONDON
S2 JONES 10 PARIS
S3 BLAKE 30 PARIS
S4 CLARK 20 LONDON
S5 ADAMS 30 ATHENS

The last query above returns the same result as the longer query statement shown below:

SELECT sno, sname, status, city
FROM supplier

When selecting columns from one or more tables or views, users may select all columns from a particular table by qualifying the special asterisk character with the table name. The sample query statement below demonstrates this approach:

SELECT supplier.*, pno, qty
FROM supplier, partsupp
WHERE supplier.sno = partsupp.sno

The result of the query statement above is the same as the statement below:

SELECT sno, sname, status, city, pno, qty
FROM supplier, partsupp
WHERE supplier.sno = partsupp.sno