SELECT

Topic group Related topics Example

Retrieves data from one or more tables.

Syntax

SELECT [DISTINCT] <column list>
FROM <table reference>
[WHERE <search condition>]
[ORDER BY <order list>]
[GROUP BY <group list>]
[HAVING <having condition>]
[UNION <select expr>]
[SAVE TO <table>]

Description

Use SELECT to retrieve data from a table or set of tables based on some criteria.

A SELECT that retrieves data from multiple tables is called a join.

<column list> is a comma-delimited list of columns in the table(s) you want to retrieve. The columns are retrieved in the order given in the list. If two or more tables used by SELECT use the same field names, distinguish the tables by using the table name and a dot ( . ). For example, if you’re SELECTing from the CUSTOMER table and the PRODUCT table, and they both have a field called NAME, enter the fields as CUSTOMER.NAME and PRODUCT.NAME in <column list>. To retrieve all the columns from <table list>, use an asterisk (*) for <column list>. To make every retrieved row unique, add the DISTINCT keyword immediately after the keyword SELECT. Using the DESCENDING, or DESC, keyword in the ORDER BY clause will retrieve the columns in descending order (Z to A, 9 to 1, later dates to earlier dates).

For example, the following statement retrieves data from two columns:

SELECT PART_NO, PART_NAME

FROM PARTS 

You may include calculated fields in the <column list>, optionally using the AS option to name them. For example:

SELECT LAST_NAME || ', ' || FIRST_NAME AS FULL_NAME, PHONE

FROM CUSTOMER 

A SELECT statement that contains a join must have a WHERE clause in which at least one field from each table is involved in an equality check.

BDE Aliases

If a BDE Alias is open as either:

A database object

  d = new Database()

  d.databaseName := "BDEAliasName"

  d.active := true )

 or

Through the use of the OPEN DATABASE command

  open database BDEAliasName )

the local SQL SELECT statement can be used to open a table:

select * from :BDEAliasName:TableName

A list of various syntax examples for the SELECT statement can be seen here: Examples