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