In addition to being able to specify desired columns,
SELECT statements can also retrieve all columns without having to list them individually. This is done using the asterisk (
*) wildcard character.
When a wildcard (
*) is specified, all the columns in the table are returned in the order in which the columns appear in the table definition. However, this cannot be relied on because changes to table schemas (adding and removing columns, for example) could cause ordering changes.
SELECT * FROM products;
Retrieving Unknown Columns There is one big advantage to using wildcards. As you do not explicitly specify column names (because the asterisk retrieves every column), it is possible to retrieve columns whose names are unknown.
Performance issue using
It is better to not using the
* wildcard unless you really do need every column in the table. Even though use of wildcards might save you the time and effort needed to list the desired columns explicitly, retrieving unnecessary columns usually slows down the performance of your retrieval and your application.
Fully Qualified Table Names
The SQL examples used thus far have referred to columns by just the column names. It is also possible to refer to columns using fully qualified names (using both the table and column names). Look at this example:
SELECT products.prod_name FROM products;
This SQL statement is functionally identical to the very first one used in this tutorial, but here a fully qualified column name is specified.
Table names, too, may be fully qualified:
SELECT products.prod_name FROM testdb.products;
There are situations where fully qualified names are required, as we will see in later tutorials.