Basic SELECT statement

 

The basic format and syntax of the SELECT statement consists of several required and optional clauses.

You can write SQL statements on one line or on many lines. For SQL statements in precompiled programs, the rules for the continuation of lines are the same as those of the host language (the language the program is written in). A SELECT statement can also be used by a cursor in a program. Finally, a SELECT statement can be prepared in a dynamic application. Notes:

1. The SQL statements described in this section can be run on SQL tables and views, and database physical and logical files.

2. Character strings specified in an SQL statement (such as those used with WHERE or VALUES clauses) are case-sensitive; that is, uppercase characters must be entered in uppercase and lowercase characters must be entered in lowercase.

   WHERE ADMRDEPT='a00'     (does not return a result)
    
   
   WHERE ADMRDEPT='A00'     (returns a valid department number)

   Comparisons might not be case sensitive if a shared-weight sort sequence is used where uppercase and lowercase characters are treated as the same characters.

A SELECT statement can include the following:

1. The name of each column you want to include in the result.

2. The name of the table or view that contains the data.

3. A search condition to identify the rows that contain the information you want.

4. The name of each column used to group your data.

5. A search condition that uniquely identifies a group that contains the information you want.

6. The order of the results so a specific row among duplicates can be returned.

A SELECT statement looks like this:

   SELECT column names      FROM table or view name      WHERE search condition      GROUP BY column names      HAVING search condition      ORDER BY column-name

The SELECT and FROM clauses must be specified. The other clauses are optional.

With the SELECT clause, you specify the name of each column you want to retrieve. For example:

   SELECT EMPNO, LASTNAME, WORKDEPT    

You can specify that only one column be retrieved, or as many as 8000 columns. The value of each column you name is retrieved in the order specified in the SELECT clause.

If you want to retrieve all columns (in the same order as they appear in the table's definition), use an asterisk (*) instead of naming the columns:

   SELECT *
   

The FROM clause specifies the table that you want to select data from. You can select columns from more than one table. When issuing a SELECT, specify a FROM clause. Issue the following statement:

SELECT *
       FROM EMPLOYEE

The result is all of the columns and rows from the table EMPLOYEE.

The SELECT list can also contain expressions, including constants, special registers, and scalar fullselects. An AS clause can be used to give the resulting column a name. For example, issue the following statement:

SELECT LASTNAME, SALARY * .05 AS RAISE        FROM EMPLOYEE        WHERE EMPNO = '200140'

The result of this statement follows.

Table 1. Results for query

LASTNAME	RAISE
NATZ	1421

 

Parent topic:

Retrieving data using the SELECT statement