The Select Statement

The purpose of the Select statement in SQL (pronounced SEQUEL) is to retrieve information from the database. The statement can be used with interactive facilities (eg. SPUFI on the mainframe) to retrieve multiple records from one or more database tables and may also be embedded into programs where it can be used to retrieve one record at a time. You need to have read access to the tables or views in order for the select to work.

SELECT [ALL | DISTINCT] select-list
[INTO host-variable, ...]
FROM table-list
[WHERE search-condition]
[GROUP BY column-name, ...]
[HAVING search-condition]
[ORDER BY [expression | integer] [ASC | DESC], ...]

Specify ALL to retrieve all rows meeting the criteria or DISTINCT to delete duplicate rows from those returned. Specifying distinct may slow the execution of the select. If neither is specified then all rows meeting the search conditions will be returned.

The selection list can contain * to return all fields from the specified table(s), a list of fields to be returned separated by commas, aggregate functions, or sub-queries. Alias names can be used (by adding alias-name after the field) to simplify references to that field subsequently in the select statement. Alias names will also be used as headings when using the select statement in an interactive environment. If fields are to be retrieved from multiple tables then any fields having common names in each table will need to be qualified as to which table the field is from (using table-name.field).

The INTO clause is used only when embedding the select statement into a program. You need to specify one host-variable for each field returned from the select to identify where the field will be loaded to in the program. You can also specify an indicator that will allow you to test for NULL value returned.

The FROM clause lists the tables that the fields are to be retrieved from. Alias names can be used (by adding AS alias-name after the table name). When specifying multiple tables you may also be able to specify the type of join to make between the tables (eg. inner, left outer, right outer).

The WHERE clause specifies the search conditions relative to the fields in the tables that should be used in order to determine which rows should be included in the results. It is also used to do joins between tables by specifying which fields in one table match to their corresponding foreign key fields in the other table.

The GROUP clause groups together multiple rows from the database. The results will contain one row for each distinct set of values in the named columns. For the purpose of grouping all NULL fields are treated as identical. Aggregate functions can be specified in conjunction with the group by clause to retrieve meaningful values relating to fields not specified in the group by clause.

The HAVING clause (like the where clause) is used to specify search conditions but whereas the where clause tests conditions on the initial database fields, the having clause tests conditions after the group by clause and aggregate functions have been applied.

The ORDER BY clause sorts the final results. Results can be sorted by several fields in a combination of ascending and descending sequence. Fields can be identified either by their position in the list of fields or by their alias (or field name if no alias is specified).

SELECT count(*) INTO :numrecs:recind FROM mytable
SELECT mark.studnum, max(mark1), max(mark2)
   FROM mark, student s
   WHERE s.age > 25
   AND mark.studnum = s.studnum
   GROUP BY mark.studnum
SELECT course, (SELECT lab FROM lab
                WHERE lab.course = course.course)
   FROM course


This article written by Stephen Chapman, Felgall Pty Ltd.

go to top

FaceBook Follow
Twitter Follow