Ways to select data from columns

Several techniques are available for selecting columns from a database for your result tables.

There are several ways to select data from the columns in your table, but you must follow good practices for SELECT statements to guarantee good performance. When you write a SELECT statement, you must select only the rows and columns that your program needs, which reduces your CPU load and memory usage.

Selection of some columns

Select the columns that you want by specifying the name of each column. All columns appear in the order that you specify, not in their order in the table.

Begin general-use programming interface information.

Example: Notice that the DEPT table contains the DEPTNO column before the MGRNO column. Consider the following query:

SELECT MGRNO, DEPTNO
	FROM DSN8A10.DEPT
	WHERE ADMRDEPT = 'A00';

The result table looks like the following example:

MGRNO   DEPTNO
======  ======
000010  A00
000020  B01
000030  C01
------  D01
000050  E01

This SELECT statement retrieves data that is contained in the two specified columns of each row in the DEPT table. You can select data from up to 750 columns with a single SELECT statement.

End general-use programming interface information.

Selection of all columns

You do not need to know the column names to select DB2® data. Use an asterisk (*) in the SELECT clause to retrieve all columns from each selected row of the specified table. DB2 selects the columns in the order that the columns are declared in that table. Hidden columns, such as ROWID columns and XML document ID columns, are not included in the result of the SELECT * statement.

Example: Consider this query:

SELECT * 
	FROM DSN8A10.DEPT
	WHERE ADMRDEPT = 'A00';

The result table looks like the following example:

DEPTNO  DEPTNAME                              MGRNO   ADMRDEPT  LOCATION
======  ========                              =====   ========  ========
A00     SPIFFY COMPUTER SERVICE DIV.          000010  A00
B01     PLANNING                              000020  A00
C01     INFORMATION CENTER                    000030  A00
D01     DEVELOPMENT CENTER                    ------  A00
E01     SUPPORT SERVICES                      000050  A00

This SELECT statement retrieves data from each column of each retrieved row of the DEPT table. Because the example does not specify a WHERE clause, the statement retrieves data from all rows.

In this example, the fifth row contains a null value because no manager is identified for this department. Null values are displayed as dashes.

The SELECT * statement is most appropriate when used with dynamic SQL and view definitions. Avoid using SELECT * in static SQL. You write static SQL applications when you know the number of columns that your application returns. That number can change outside your application. If a change occurs to the table, you need to update the application to reflect the changed number of columns in the table.

Use the SELECT * statement only when it is necessary to retrieve all the columns in each retrieved row of your table. Selecting specific columns give your query a higher filter that can retrieve your results more efficiently.

Elimination of duplicate rows

The DISTINCT keyword removes redundant duplicate rows from your result table so that each row contains unique data. The following query uses the DISTINCT keyword to list the department numbers of the different administrative departments:

SELECT DISTINCT ADMRDEPT
  FROM DSN8A10.DEPT;

The result table looks like the following example:

ADMRDEPT
========
A00
D11
E01

You can use more than one DISTINCT keyword in a single query.

Selection of derived columns and naming the resulting columns

You can select columns that are derived from a constant, an expression, or a function. With the AS clause, you can name resulting columns. This keyword is useful for a column that is derived from an expression or a function.

Example: In the following query, the expression SALARY+COMM is named TOTAL_SAL:

SELECT EMPNO, (SALARY + COMM) AS TOTAL_SAL
	FROM DSN8A10.EMP;

The result table looks like the following example:

EMPNO      TOTAL_SAL
======     =========
000290      16567.00
000310      17172.00
200310      17172.00
000260      18630.00
000300      19170.00
000210      19732.00
⋮

This query selects data from all rows in the EMP table, calculates the result of the expression, and returns the columns in the order that the SELECT statement indicates. In the result table, any derived columns, such as (SALARY + COMM) in this example, do not have names. You can use the AS clause to give names to unnamed columns.

To order the rows in the result table by the values in a derived column, specify a name for the column by using the AS clause and use that name in the ORDER BY clause.