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 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.

• 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 recursive common table expression or a recursive view.

• 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

 

Related reference

Creating a table using AS
Creating and using views