Retrieving data using the SELECT statement
The SELECT statement tailors your query to gather data. You can use the SELECT statement to retrieve a specific row or retrieve data in a specific way.
If SQL is unable to find a row that satisfies the search condition, an SQLCODE of +100 is returned.
If SQL finds errors while running your select-statement, a negative SQLCODE is returned. If SQL finds more host variables than results, +326 is returned.
- Basic SELECT statement
The basic format and syntax of the SELECT statement consists of several required and optional clauses. - Specifying a search condition using the WHERE clause
The WHERE clause specifies a search condition that identifies the row or rows that you want to retrieve, update, or delete. - GROUP BY clause
The GROUP BY clause allows you to find the characteristics of groups of rows rather than individual rows. - HAVING clause
The HAVING clause specifies a search condition for the groups selected by the GROUP BY clause. - ORDER BY clause
The ORDER BY clause specifies the particular order in which you want selected rows returned. The order is sorted by ascending or descending collating sequence of a column's or an expression's value. - Static SELECT statements
For a static SELECT statement (one embedded in an SQL program), an INTO clause must be specified before the FROM clause. - Handling null values
A null value indicates the absence of a column value in a row. A null value is an unknown value; it is not the same as zero or all blanks. - Special registers in SQL statements
You can specify certain special registers in SQL statements. A special register, for example, CURRENT DATE, contains information that can be referenced in SQL statements. - Casting data types
Sometimes you need to cast or change the type of an expression to a different data type or to the same data type with a different length, precision, or scale. - Date, time, and timestamp data types
Date, time, and timestamp are data types that are represented in an internal form not seen by an SQL user. - Row change expressions
The ROW CHANGE TIMESTAMP and ROW CHANGE TOKEN expressions can be used to determine when a row was last changed. - Handling duplicate rows
When SQL evaluates a select-statement, several rows might qualify to be in the result table, depending on the number of rows that satisfy the search condition of the select-statement. Some of the rows in the result table might be duplicate. - Defining complex search conditions
In addition to the basic comparison predicates, such as = and >, a search condition can contain any of these predicates: BETWEEN, IN, EXISTS, IS NULL, and LIKE. - Using OLAP specifications
Online analytical processing (OLAP) specifications are used to return ranking numbers and row numbers for the result rows of a query. You can specify RANK, DENSE_RANK, and ROW_NUMBER. - Joining data from more than one table
Sometimes the information you want to see is not in a single table. To form a row of the result table, you might want to retrieve some column values from one table and some column values from another table. You can retrieve and join column values from two or more tables into a single row. - Using table expressions
You can use table expressions to specify an intermediate result table. - Using recursive queries
Some applications work with data that is recursive in nature. To query this type of data, you can use a hierarchical query or a recursive common table expression. - Using the UNION keyword to combine subselects
Using the UNION keyword, you can combine two or more subselects to form a fullselect. - Using the EXCEPT keyword
The EXCEPT keyword returns the result set of the first subselect minus any matching rows from the second subselect. - Using the INTERSECT keyword
The INTERSECT keyword returns a combined result set that consists of all of the rows existing in both result sets. - Data retrieval errors
Use this information to understand how SQL handles errors that occur when retrieving data.
Parent topic: Data manipulation language