<< Click to Display Table of Contents >> Navigation:  Local SQL > Data Manipulation Language > Statements > SELECT statement

Retrieves data from tables.

SELECT [DISTINCT] * | column_list

FROM table_reference

[WHERE predicates]

[GROUP BY group_list]

[HAVING having_condition]

[ORDER BY order_list]

Description

Use the SELECT statement to retrieve zero, one, or multiple rows into a dynamic result set. The rows retrieved may come from one or multiple tables (the latter referred to as a "join"). The statement may retrieve data from all or only some of the columns in the source table(s).

The SELECT clause indidcates the columns from which data is retrieved and that compose the result set. Specify the columns of the result set with a comma-separated list comprising: table columns, literal values, and calculated values. Literal values in the columns list may be passed to the SELECT statement via parameters. You cannot use parameters to pass column names to the SQL statement. Use an asterisk to retrieve values from all columns. Columns in the columns list in the SELECT clause may come from more than one table, but can only come from those tables listed in the FROM clause. See Relational Operators for more information on using the SELECT statement to retrieve data from multiple tables (joining tables).

The FROM clause identifies the table(s) from which data is retrieved.

The following statement retrieves data from two columns (CustNo and Company), plus a literal value ("Current") and a calculated value (the date), for all rows of a single table (Customer).

SELECT CustNo, Company, "Current", CAST(LastInvoiceDate AS DATE)

FROM Customer

Use DISTINCT to limit the retrieved data to only distinct rows. The distinctness of rows is based on the combination of all of the columns in the SELECT clause columns list. DISTINCT can only be used with simple column types like CHAR and INTEGER; it cannot be used with complex column types like BLOB and memo.

SELECT DISTINCT CustNo, EXTRACT(YEAR FROM SaleDate) AS Yr

FROM Orders

In lieu of a table, a SELECT statement may retrieve rows from an SQL statement (or Paradox-style .QBE query) that has been saved to a file. See the topic Local SQL VIEW for more information on this.

To limit the rows retrieved by a SELECT statement to only those meeting a specified logical criteria, use a WHERE clause in the statement. To cause aggregation to be performed once for each group of like rows, use a GROUP BY clause. To limit rows retrieved based on agregated values, use a HAVING clause. And to force the retrieved rows to be ordered a certain way in the result set, use an ORDER BY clause. Not all clauses must be in a given SELECT statement, but when they are they must appear in this specific order: SELECT (required), FROM (required), WHERE (optional), GROUP BY (optional), HAVING (optional), and ORDER BY (optional).

SELECT statements can be used as subqueries in INSERT, DELETE, and UPDATE statements. See the topics for these statements for specific requirements for using SELECT subqueries. SELECT subqueries can be used in SELECT statements, but are limited to the WHERE and HAVING clauses. They cannot be used in the SELECT, FROM, GROUP BY, or ORDER BY clauses of a SELECT statement.

Note: There are special limitations on what columns can appear in the SELECT clause of a grouped query. See the GROUP BY clause for more information.