Retrieving data by using the SELECT statement

Consider developing SQL statements similar to the examples in this section, and then running them dynamically using SPUFI, the command line processor, or DB2® Query Management Facility™ (QMF™) (QMF).

You do not need to know the column names to select DB2 data. Use an asterisk (*) in the SELECT clause to indicate that you want to retrieve all columns of each selected row of the named table. Implicitly hidden columns, such as ROWID columns and XML document ID columns, are not included in the result of the SELECT * statement. To view the values of these columns, you must specify the column name.

SELECT *
  FROM DSN8A10.DEPT;

The result table looks similar to the following output:

DEPTNO  DEPTNAME                         MGRNO      ADMRDEPT  LOCATION
======  ==============================   ======     ========  ========
A00     SPIFFY COMPUTER SERVICES DIV.    000010     A00       --------
B01     PLANNING                         000020     A00       --------
C01     INFORMATION CENTER               000030     A00       --------
D01     DEVELOPMENT CENTER               ------     A00       --------
D11     MANUFACTURING CENTER             000060     D01       --------
D21     ADMINISTRATION SYSTEMS           000070     D01       --------
E01     SUPPORT SERVICES                 000050     A00       --------
E11     OPERATIONS                       000090     E01       --------
E21     SOFTWARE SUPPORT                 000100     E01       --------
F22     BRANCH OFFICE F2                 ------     E01       --------
G22     BRANCH OFFICE G2                 ------     E01       --------
H22     BRANCH OFFICE H2                 ------     E01       --------
I22     BRANCH OFFICE I2                 ------     E01       --------
J22     BRANCH OFFICE J2                 ------     E01       --------

Because the example does not specify a WHERE clause, the statement retrieves data from all rows.

The dashes for MGRNO and LOCATION in the result table indicate null values.

SELECT * is recommended mostly for use with dynamic SQL and view definitions. You can use SELECT * in static SQL, but doing so is not recommended because of host variable compatibility and performance reasons.Suppose that you add a column to the table to which SELECT * refers. If you have not defined a receiving host variable for that column, an error occurs.

If you list the column names in a static SELECT statement instead of using an asterisk, you can avoid the problem that sometimes occurs with SELECT *. You can also see the relationship between the receiving host variables and the columns in the result table.

Select the column or columns you want to retrieve by naming each column. All columns appear in the order you specify, not in their order in the table.

SELECT MGRNO, DEPTNO
  FROM DSN8A10.DEPT;

The result table looks similar to the following output:

MGRNO     DEPTNO
======    ======
000010    A00
000020    B01
000030    C01
------    D01
000050    E01
000060    D11
000070    D21
000090    E11
000100    E21
------    F22
------    G22
------    H22
------    I22
------    J22

With a single SELECT statement, you can select data from one column or as many as 750 columns.

To SELECT data from implicitly hidden columns, such as ROWID and XML document ID, look up the column names in SYSIBM.SYSCOLUMNS and specify these names in the SELECT list. For example, suppose that you create and populate the following table:

CREATE TABLE MEMBERS (MEMBERID INTEGER,
                      BIO 		 XML,
                      REPORT 	 XML,
                      RECOMMENDATIONS 	XML);

DB2 generates one additional implicitly hidden XML document ID column. To retrieve data in all columns, including the generated XML document ID column, first look up the name of the generated column in SYSIBM.SYSCOLUMNS. Suppose the name is DB2_GENERATED_DOCID_FOR_XML. Then, specify the following statement:

SELECT DB2_GENERATED_DOCID_FOR_XML, MEMBERID, BIO,
       REPORT, RECOMMENDATIONS FROM MEMBERS

Use a WHERE clause to select the rows that meet certain conditions. A WHERE clause specifies a search condition. A search condition consists of one or more predicates. A predicate specifies a test that you want DB2 to apply to each table row.

DB2 evaluates a predicate for each row as true, false, or unknown. Results are unknown only if an operand is null.

If a search condition contains a column of a distinct type, the value to which that column is compared must be of the same distinct type, or you must cast the value to the distinct type.

The following table lists the type of comparison, the comparison operators, and an example of each type of comparison that you can use in a predicate in a WHERE clause.

You can also search for rows that do not satisfy one of the preceding conditions by using the NOT keyword before the specified condition.

You can search for rows that do not satisfy the IS DISTINCT FROM predicate by using either of the following predicates:

• value 1 IS NOT DISTINCT FROM value 2
• NOT(value 1 IS DISTINCT FROM value 2)

Both of these forms of the predicate create an expression for which one value is equal to another value or both values are equal to null.