SQL Pocket Guide, 3rd Edition

Selecting Data

Use a SELECT statement, or query, to retrieve data from a database—typically from a table or view or from a combination of tables and views:

SELECT expression_list
FROM data_source
WHERE predicates
GROUP BY expression_list
HAVING predicates
ORDER BY expression_list

DB2, Oracle, PostgreSQL, and SQL Server support factoring out subqueries using a WITH clause. See Hierarchical Queries and Subqueries for some examples of this technique.

The SELECT Clause

Each expression in the SELECT clause becomes a column in the result set returned by the query. Expressions may be simple column names, may generate a new value using a column value as input, or may have nothing to do with any columns at all.

Listing the columns to retrieve

The SELECT clause specifies the individual data elements you want the statement to return. The simple case is to specify a comma-delimited list of one or more column names from the tables listed in the FROM clause:

SELECT id, name
FROM owner;

The result set for this query will contain the following columns:

ID           NAME
------------ ---------------
1            Pictured Rocks
2            Michigan Nature
3            AF LLC
4            MI DNR
5            Horseshoe Falls

Taking shortcuts with the asterisk

To return all columns from a table, you can specify a single asterisk rather than write out each column name:

SELECT *
FROM owner; ID NAME PHONE TYPE ------------ --------------- ------------ ------- 1 Pictured Rocks 906.387.2607 public 2 Michigan Nature 517.655.5655 private 3 AF LLC private 4 MI DNR 906-228-6561 public ...