Section 9.1. Querying DB2 Data

9.1. Querying DB2 Data

You use the SELECT statement to query tables or views from a database. At a minimum, the statement contains a SELECT clause and a FROM clause. The following are two examples of a SELECT statement. This first example uses the wildcard symbol (*) to indicate that all columns from the employee table are selected:

 SELECT * FROM employee; 

In this example, the column names are specified in the SELECT statement:

 SELECT empno, firstnme, lastname FROM employee; 

9.1.1. Derived Columns

When data is retrieved from a table using the SELECT clause, you can derive new columns based on other columns. Figure 9.1 illustrates this: the column totalpay is derived by adding the salary and comm columns.

Figure 9.1. Example of a derived column

 SELECT empno, firstnme, lastname, (salary + comm) AS totalpay   FROM employee EMPNO  FIRSTNME     LASTNAME        TOTALPAY ------ ------------ --------------- ------------ 000010 CHRISTINE    HAAS                 4320.00 000020 MICHAEL      THOMPSON            44550.00 000030 SALLY        KWAN                41310.00 000050 JOHN         GEYER               43389.00 000060 IRVING       STERN               34830.00 . . . 

9.1.2. The SELECT COUNT Statement

The SELECT COUNT statement lets you get a row count of the result set. For example, the SQL statement in Figure 9.2 returns the number of rows in the sales table whose region column has the value Quebec. In this case, there are 12 records that match this criteria.

Figure 9.2. Example of a SELECT COUNT statement

 SELECT COUNT(*)   FROM sales  WHERE region = 'Quebec' 1 -----------          12   1 record(s) selected. 

9.1.3. The SELECT DISTINCT Statement

To eliminate duplicate rows in a result set, use the DISTINCT keyword in the SELECT statement. The SQL statement in Figure 9.3 selects the distinct values of the region column from the sales table.

Figure 9.3. Example of a SELECT DISTINCT statement

 SELECT DISTINCT region FROM sales REGION --------------- Manitoba Ontario-North Ontario-South Quebec   4 record(s) selected. 

You can also use the DISTINCT keyword with the SELECT COUNT statement. For example, the SQL statement in Figure 9.4 returns the number of distinct regions in the sales table.

Figure 9.4. Example of a SELECT COUNT DISTINCT statement

 SELECT COUNT (DISTINCT region) FROM sales 1 -----------           4   1 record(s) selected. 

The output shows that there are four distinct regions in the sales table. This value agrees with the SELECT DISTINCT region FROM sales result obtained in Figure 9.3.

9.1.4. DB2 Special Registers

DB2 special registers are memory registers that allow DB2 to provide information to an application about its environment. They can be referenced in SQL statements. The most commonly used special registers are listed in Table 9.1. For a complete list of DB2 special registers, refer to the DB2 UDB SQL Reference Guide.

Table 9.1. DB2 Special Registers

DB2 Special Registers	Descriptions
CURRENT DATE	A date based on the time-of-day clock at the database server. If this register is referenced more than once in a single statement, the value returned will be the same for all references.
CURRENT ISOLATION	Identifies the isolation level for any dynamic SQL statements issued within the current session.
CURRENT LOCK TIMEOUT	Specifies the number of seconds that an application will wait to obtain a lock.
CURRENT PACKAGE PATH	Identifies the path to be used when resolving references to packages.
CURRENT PATH	Identifies the SQL path used to resolve procedure, functions, and data type references for dynamically prepared SQL statements. The value of this special register is a list of one or more schema names.
CURRENT SCHEMA	Identifies the schema name used to qualify unqualified database objects in dynamic SQL statements. The default value is the authorization ID of the current user. This special register can be modified using the SET CURRENT SCHEMA statement.
CURRENT TIME	A time based on the time-of-day clock at the database server. If this register is referenced more than once in a single statement, the value returned will be the same for all references.
CURRENT TIMESTAMP	A timestamp based on the time-of-day clock at the database server. If this register is referenced more than once in a single statement, the value returned will be the same for all references.
SESSION_USER	Specifies the authorization ID to be used for the current session. This is the same as the USER special register.
SYSTEM_USER	Specifies the authorization ID of the user who connected to the database.
USER	Specifies the runtime authorization ID used to connect to the database.

To display the value of a special register, use the following statement:

 VALUES special_register 

For example, to display the value of the CURRENT TIMESTAMP special register, issue:

 VALUES CURRENT TIMESTAMP 

SQL also supports expressions using DB2 special registers. Figure 9.5 uses the CURRENT DATE register to derive the retiredate column.

Some of the special registers are updatable. For example, to change the value of the CURRENT ISOLATION special register to RR (Repeatable Read), issue:

 SET CURRENT ISOLATION RR 

Figure 9.5. Example of using DB2 special registers in a SELECT statement

 SELECT empno, firstnme, lastname      , (salary + comm) AS totalpay      , CURRENT DATE AS retiredate   FROM employee EMPNO  FIRSTNME     LASTNAME        TOTALPAY     RETIREDATE ------ ------------ --------------- ------------ ---------- 000010 CHRISTINE    HAAS                 4320.00 04/01/2005 000020 MICHAEL      THOMPSON            44550.00 04/01/2005 000030 SALLY        KWAN                41310.00 04/01/2005 000050 JOHN         GEYER               43389.00 04/01/2005 000060 IRVING       STERN               34830.00 04/01/2005 . . . 

9.1.5. Scalar and Column Functions

Invoking a function against the column values is also very useful. Consider the following example where you want to obtain the name of the day for each employee's hire date. You can use the DAYNAME built-in function supplied by DB2 as shown in Figure 9.6.

Figure 9.6. Example of a scalar function

 SELECT empno, firstnme, lastname      , (salary + comm) AS totalpay      , DAYNAME(hiredate) AS dayname   FROM employee EMPNO  FIRSTNME     LASTNAME        TOTALPAY     DAYNAME ------ ------------ --------------- ------------ ------------ 000010 CHRISTINE    HAAS                 4320.00 Friday 000020 MICHAEL      THOMPSON            44550.00 Wednesday 000030 SALLY        KWAN                41310.00 Saturday 000050 JOHN         GEYER               43389.00 Wednesday 000060 IRVING       STERN               34830.00 Friday . . . 

The function DAYNAME used in Figure 9.6 is called a scalar function. A scalar function takes input values and returns a single value. Another type of function, called a column function, operates on the values of an entire column. The example in Figure 9.7 shows how to calculate the average values of the salary column.

The AVG column function, which is a built-in function, calculates the average of all the salary values in the employee table. Notice that the DECIMAL function is also used; this casts the average result to a decimal representation with a precision of 9, and scale of 2.

Figure 9.7. Example of a column function

 SELECT DECIMAL( AVG(salary), 9, 2 ) AS avgsalary   FROM employee AVGSALARY -----------    25658.28   1 record(s) selected. 

9.1.6. The CAST Expression

There are many occasions where a value with a given data type needs to be cast to a different data type. For example, when manipulating data using the DATE and TIMESTAMP data types, TIMESTAMP might need to be cast to DATE. Figure 9.8 illustrates such an example.

Figure 9.8. Example of a CAST expression

 SELECT CURRENT TIMESTAMP, CAST(CURRENT TIMESTAMP AS DATE)   FROM SYSIBM.SYSDUMMY1 1                          2 -------------------------- ---------- 2005-04-01-17.00.24.637001 04/01/2005   1 record(s) selected. 

9.1.7. The WHERE clause

For better performance, you should always write your SQL statements so that only the required data is returned. One way to achieve this is to limit the number of columns to be retrieved by explicitly specifying the column names in the SELECT statement (as illustrated in previous examples). The other way is to limit the number of rows to be retrieved using the WHERE clause. Figure 9.9 illustrates an example of a SELECT statement that returns employees who are managers with a salary greater than $1,000.

Figure 9.9. Example of a WHERE clause

 SELECT empno, firstnme, lastname   FROM employee  WHERE salary > 1000    AND job = 'MANAGER' EMPNO  FIRSTNME     LASTNAME ------ ------------ --------------- 000020 MICHAEL      THOMPSON 000030 SALLY        KWAN 000050 JOHN         GEYER 000060 IRVING       STERN 000070 EVA          PULASKI 000090 EILEEN       HENDERSON 000100 THEODORE     SPENSER   7 record(s) selected. 

9.1.8. Using FETCH FIRST n ROWS ONLY

Sometimes you may want to obtain just the first few rows from the result set. Use the FETCH FIRST n ROWS ONLY clause of the SELECT statement to accomplish this. For example, to limit only three rows to be returned from the example illustrated in Figure 9.9, use the statement shown in Figure 9.10.

Figure 9.10. Example of FETCH FIRST n ROWS ONLY

 SELECT empno, firstnme, lastname   FROM employee  WHERE workdept > 'A0'    AND job = 'MANAGER'  FETCH FIRST 3 ROWS ONLY EMPNO  FIRSTNME     LASTNAME ------ ------------ --------------- 000020 MICHAEL      THOMPSON 000030 SALLY        KWAN 000050 JOHN         GEYER   3 record(s) selected. 

9.1.9. The LIKE Predicate

The LIKE predicate lets you search for patterns in character string columns. For example, the SQL statement in Figure 9.11 returns all the rows for employees whose last name starts with the letter M in the employee table.

Figure 9.11. Example of a LIKE predicate

 SELECT empno, firstnme, lastname FROM employee WHERE lastname LIKE 'M%' OR workdept LIKE 'D2_' EMPNO  FIRSTNME     LASTNAME        WORKDEPT ------ ------------ --------------- -------- 000230 JAMES        JEFFERSON       D21 000260 SYBIL        JOHNSON         D21   2 record(s) selected. 

In SQL, the percent sign (%) is a wildcard character that represents zero or more characters. It can be used any place in the search string, and as many times as you need it.

The other wildcard character used with the LIKE predicate is the underline character (_). This character represents one and only one character. In Figure 9.11, it matches items in workdept that have strings exactly three characters long, with the first two characters of D2.

9.1.10. The BETWEEN Predicate

The BETWEEN predicate lets you search for all the rows whose value falls between the values it indicates. For example, the SQL statement in Figure 9.12 returns all the rows from the employee table whose salary is between $40,000 and $50,000.

Figure 9.12. Example of a BETWEEN predicate

 SELECT firstnme, lastname, salary FROM employee WHERE salary BETWEEN 40000 AND 50000 FIRSTNME     LASTNAME        SALARY ------------ --------------- ----------- MICHAEL      THOMPSON           41250.00 JOHN         GEYER              40175.00 VINCENZO     LUCCHESSI          46500.00   3 record(s) selected. 

9.1.11. The IN Predicate

The IN predicate lets you search rows based on a set of values. The SQL statement in Figure 9.13 returns all the rows from the sales table whose value in the sales_date column is either 12/31/1995 or 03/29/1996.

Figure 9.13. Example of an IN predicate

 SELECT * FROM sales WHERE sales_date IN ('12/31/1995', '03/29/1996') SALES_DATE SALES_PERSON    REGION          SALES ---------- --------------- --------------- ----------- 12/31/1995 LUCCHESSI       Ontario-South             1 12/31/1995 LEE             Ontario-South             3 12/31/1995 LEE             Quebec                    1 12/31/1995 LEE             Manitoba                  2 12/31/1995 GOUNOT          Quebec                    1 03/29/1996 LUCCHESSI       Ontario-South             3 03/29/1996 LUCCHESSI       Quebec                    1 03/29/1996 LEE             Ontario-South             2 03/29/1996 LEE             Ontario-North             2 03/29/1996 LEE             Quebec                    3 03/29/1996 LEE             Manitoba                  5 03/29/1996 GOUNOT          Ontario-South             3 03/29/1996 GOUNOT          Quebec                    1 03/29/1996 GOUNOT          Manitoba                  7   14 record(s) selected. 

9.1.12. The ORDER BY Clause

SQL does not return the results retrieved in a particular order; the order of a result may be different each time when the same SELECT statement is executed. To sort the result set, use the ORDER BY clause as shown in Figure 9.14.

Figure 9.14. Example of an ORDER BY clause

 SELECT empno, firstnme, lastname   FROM employee  WHERE job='MANAGER'  ORDER BY lastname EMPNO  FIRSTNME     LASTNAME ------ ------------ --------------- 000050 JOHN         GEYER 000090 EILEEN       HENDERSON 000030 SALLY        KWAN 000070 EVA          PULASKI 000100 THEODORE     SPENSER 000060 IRVING       STERN 000020 MICHAEL      THOMPSON   7 record(s) selected. 

Note that you must specify the column names in the ORDER BY clause; column numbers are not allowed.

9.1.13. The GROUP BY...HAVING Clause

When you need to group multiple rows into a single row based on one or more columns, the GROUP BY clause comes in handy. Figure 9.15 shows an example that sums up the salary of all the employees in each department. The HAVING clause specifies which of the combined rows are to be retrieved. In the statement in Figure 9.15, only department names starting with E are retrieved.

Figure 9.15. Example of GROUP BY and HAVING clauses

 SELECT workdept, SUM(salary) AS total_salary   FROM employee  GROUP BY workdept HAVING workdept LIKE 'E%' WORKDEPT TOTAL_SALARY -------- --------------------------------- E01                               40175.00 E11                              104990.00 E21                               95310.00   3 record(s) selected. 

9.1.14. Joins

Sometimes information that you want to retrieve does not reside in a single table. You can join two or more tables in a SELECT statement. Consider the example in Figure 9.16.

Figure 9.16. Example of an INNER join

 SELECT empno, firstnme, lastname, deptname   FROM employee, department  WHERE workdept = deptno    AND admrdept='A00' EMPNO  FIRSTNME     LASTNAME        DEPTNAME                      MGRNO ------ ------------ --------------- ----------------------------- ----- 000010 CHRISTINE    HAAS            SPIFFY COMPUTER SERVICE DIV.  00010 000110 VINCENZO     LUCCHESSI       SPIFFY COMPUTER SERVICE DIV.  00010 000120 SEAN         O'CONNELL       SPIFFY COMPUTER SERVICE DIV.  00010 000020 MICHAEL      THOMPSON        PLANNING                      00020 000030 SALLY        KWAN            INFORMATION CENTER            00030 000130 DOLORES      QUINTANA        INFORMATION CENTER            00030 000140 HEATHER      NICHOLLS        INFORMATION CENTER            00030 000050 JOHN         GEYER           SUPPORT SERVICES              00050    8 record(s) selected. 

The example in Figure 9.16 retrieves a list of employees, their department names, and manager's employee numbers whose administrative department is A00. Since the employee table only stores the department number of the employees but not the department names, you need to join the employee table with the department table. Note that the two tables are joined in the FROM clause. Records with matching department numbers (workdept = deptno) are retrieved.

This type of join is called an inner join; it results in matched rows that are present in both joined tables. The INNER JOIN keywords can be omitted as demonstrated in Figure 9.16. However, if you choose to explicitly use the INNER JOIN syntax, the SELECT statement in Figure 9.16 can be rewritten as the following:

 SELECT empno, firstnme, lastname, deptname   FROM employee INNER JOIN department     ON workdept = deptno  WHERE admrdept='A00' 

Note that INNER JOIN is used in the FROM clause. The ON keyword specifies the join predicates and categorizes rows as either joined or not-joined. This is different from the WHERE clause, which is used to filter rows.

There are three other types of joins: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. Outer joins are useful when you want to include rows that are present in the left table, right table, or both tables, in addition to the rows returned from the implied inner join. A table specified on the left side of the OUTER JOIN operator is considered the left table, and the table specified on the right side of the OUTER JOIN operator is considered the right table.

A left outer join includes rows from the left table that were missing from the inner join. A right outer join includes rows from the right table that were missing from the inner join. A full outer join includes rows from both the left and right tables that were missing from the inner join. Figures 9.17, 9.18, and 9.19 demonstrate information to be retrieved and an example of each join.

Figure 9.17. Example of a LEFT OUTER join

Figure 9.18. Example of a RIGHT OUTER join

Figure 9.19. Example of a FULL OUTER join

9.1.15. Working with NULLs

A NULL in DB2 represents an unknown value. The following is an example of how to check if a value is NULL:

 SELECT empno FROM employee WHERE midinit IS NULL 

When working with NULL values, the COALESCE function comes in very handy: It checks whether the input is NULL. The value of the input is returned if it is not NULL, otherwise it returns the value provided in the second expression of the COALESCE function. Refer to Figure 9.20 for an example that returns 0 if comm is NULL.

Figure 9.20. Example of the COALESCE function

 SELECT id, name, COALESCE(comm, 0) AS comm   FROM staff  FETCH FIRST 6 ROWS ONLY ID     NAME      COMM ------ --------- ---------------     10 Sanders              0.00     20 Pernal             612.45     30 Marenghi             0.00     40 O'Brien            846.55     50 Hanes                0.00     60 Quigley            650.25   6 record(s) selected. 

9.1.16. The CASE Expression

When you want to perform a particular operation depending on the evaluation of a value, you can use a CASE expression to simplify your code. The example in Figure 9.21 introduces this expression.

Figure 9.21. Example of a CASE expression

 SELECT firstnme, lastname,   CASE     WHEN salary < 10000 THEN 'Need a raise'     WHEN salary > 10000 AND salary < 20000 THEN 'Fair pay'     ELSE 'Overpaid'   END AS comment FROM employee FIRSTNME     LASTNAME        COMMENT ------------ --------------- ------------ CHRISTINE    HAAS            Need a raise MICHAEL      THOMPSON        Overpaid SALLY        KWAN            Overpaid JOHN         GEYER           Overpaid IRVING       STERN           Overpaid WILLIAM      JONES           Fair pay   6 record(s) selected. 

In Figure 9.21, the values of the salary column are evaluated. If the value is less than $10,000, the string Need a raise is returned. If the value is between $10,000 and $20,000, Fair pay is returned. For all other values, Overpaid is returned.

9.1.17. Adding a Row Number to the Result Set

Recall that the FETCH FIRST n ROWS ONLY clause lets you return only the first n rows. What if you want to return row 30 or more? The ROWNUMBER and OVER functions solve this problem. Figure 9.22 shows a column derived with sequential row numbers generated by ROWNUMBER() OVER().

Figure 9.22. Example 1: Using ROWNUMBER() OVER()

 SELECT ROWNUMBER() OVER() AS rowid, firstnme, lastname FROM employee ROWID                FIRSTNME     LASTNAME -------------------- ------------ ---------------                    1 CHRISTINE    HAAS                    2 MICHAEL      THOMPSON                    3 SALLY        KWAN                    4 JOHN         GEYER                    5 IRVING       STERN                    6 EVA          PULASKI                    7 EILEEN       HENDERSON                    8 THEODORE     SPENSER                    9 VINCENZO     LUCCHESSI                   10 SEAN         O'CONNELL                   11 DOLORES      QUINTANA                   12 HEATHER      NICHOLLS                   13 BRUCE        ADAMSON                   14 ELIZABETH    PIANKA                   15 MASATOSHI    YOSHIMURA                   16 MARILYN      SCOUTTEN                   17 JAMES        WALKER                   18 DAVID        BROWN                   19 WILLIAM      JONES                   20 JENNIFER     LUTZ                   21 JAMES        JEFFERSON                   22 SALVATORE    MARINO                   23 DANIEL       SMITH                   24 SYBIL        JOHNSON                   25 MARIA        PEREZ                   26 ETHEL        SCHNEIDER                   27 JOHN         PARKER                   28 PHILIP       SMITH                   29 MAUDE        SETRIGHT                   30 RAMLAL       MEHTA                   31 WING         LEE                   32 JASON        GOUNOT   32 record(s) selected. 

To return rows higher than 30, use the ROWNUMBER()OVER() expression to the FROM clause. Figure 9.23 shows this trick.

Figure 9.23. Example 2: Using ROWNUMBER() OVER()

 SELECT rowid, firstnme, lastname FROM ( SELECT ROWNUMBER() OVER() AS rowid, firstnme, lastname          FROM employee) AS temp WHERE rowid > 30 ROWID                FIRSTNME     LASTNAME -------------------- ------------ ---------------                   31 WING         LEE                   32 JASON        GOUNOT   2 record(s) selected. 

You can also sort the result set before numbering the rows, as shown in Figure 9.24.

Figure 9.24. Example 3: Using ROWNUMBER() OVER()

 SELECT rowid, firstnme, lastname FROM ( SELECT ROWNUMBER() OVER( ORDER BY salary, comm ) AS rowid,              firstnme, lastname          FROM employee) AS temp WHERE rowid > 30 ROWID                FIRSTNME     LASTNAME -------------------- ------------ ---------------                   31 MICHAEL      THOMPSON                   32 VINCENZO     LUCCHESSI   2 record(s) selected.