SQL Foundations  «Prev 

Selecting Information from Database Table

Querying Database Tables Using SELECT

The following sections examine database queries using the SELECT command in detail, as well as by example.

Basic Queries

The following syntax shows the structure of the SELECT statement and the FROM clause. The SELECT list is the list of fields, or otherwise, usually retrieved from tables. The FROM clause specifies one or more tables from which to retrieve data.

SELECT { [alias.]field | expression | [alias.]* [,the rest of the list of fields] }
FROM table [alias] [ , ... ];

The easiest way to understand SQL is by example. If you want to retrieve all fields from the AUTHOR table using the * (star or asterisk) character.
The * character tells the query to retrieve all fields in all tables in the FROM clause:

---------- --------------------------------
1 Orson Scott Card
2 James Blish
3 Isaac Azimov
4 Larry Niven
5 Jerry Pournelle
6 William Shakespeare
7 Kurt Vonnegut

A semi-colon and carriage return is used to end the query command, submitting the query to the query engine. Not all relational databases execute on a semi-colon. Some databases use a different character; others just a carriage-return.

Use of the SELECT *

Use of the

form of the SQL SELECT clause is acceptable in situations where the specific columns involved, and their left to right ordering, are both irrelevant, for example, in an EXISTS invocation. It can be dangerous in other situations, however, because the meaning of that wildcard * can change if (for example) new columns are added to an existing table.
Recommendation: Be on the lookout for such situations and try to avoid them. In particular, do not use
at the outermost level in a cursor definition. Instead, always name the pertinent columns explicitly.
A similar remark applies to view definitions also.