DB2 SELECT Statement: Complete Guide with Examples (2026)

Introduction: Why the DB2 SELECT Statement Is Your Most Important SQL Tool

If you work with IBM DB2 — whether on a mainframe z/OS environment or a distributed LUW (Linux, Unix, Windows) system — the DB2 SELECT statement is the single most critical piece of SQL you will write. Every report, every batch job, every CICS screen that displays data ultimately begins with a SELECT. Despite its apparent simplicity, the DB2 SELECT statement has considerable depth: it handles column expressions, built-in functions, NULL semantics, schema qualifiers, date/time arithmetic, and performance-controlling clauses like FETCH FIRST that separate a fast query from a runaway one.

This guide walks through every major facet of the DB2 SELECT statement with technically accurate, copy-paste-ready examples drawn from the classic IBM sample database tables — EMPLOYEE, DEPARTMENT, and PROJECT. Whether you are a COBOL developer embedding SQL in a host program, a data analyst running ad hoc queries in SPUFI, or a Java developer using JDBC against DB2, this reference has you covered.

Basic SELECT Syntax in DB2

The minimal form of a DB2 SELECT retrieves one or more columns from a single table. Every DB2 table is qualified by a schema name (also called a high-level qualifier or HLQ on z/OS), which must be included unless a default schema is configured for your session.

-- Retrieve all columns from the EMPLOYEE table
SELECT *
FROM   EMP.EMPLOYEE;

-- Retrieve specific columns only (preferred in production)
SELECT EMPNO,
       FIRSTNME,
       LASTNAME,
       WORKDEPT,
       SALARY
FROM   EMP.EMPLOYEE;

While SELECT * is convenient for exploration, always name your columns explicitly in production code. When a DBA adds or reorders columns on the table, SELECT * in a compiled program can cause buffer overflows or incorrect data mapping in host language programs.

The DB2 optimizer processes the FROM clause first, then the WHERE filter, then the SELECT projection. Understanding this order of operations helps you write queries that the optimizer can execute efficiently.

Filtering Rows with the WHERE Clause

The WHERE clause is the primary mechanism for narrowing a result set. DB2 supports all standard SQL comparison operators (=, <>, <, >, <=, >=), as well as BETWEEN, IN, LIKE, NOT, and the NULL-specific predicates IS NULL and IS NOT NULL.

-- Employees in department A00
SELECT EMPNO, LASTNAME, WORKDEPT
FROM   EMP.EMPLOYEE
WHERE  WORKDEPT = 'A00';

-- Employees with salary between 50,000 and 80,000
SELECT EMPNO, LASTNAME, SALARY
FROM   EMP.EMPLOYEE
WHERE  SALARY BETWEEN 50000.00 AND 80000.00;

-- Employees whose last name starts with 'H'
SELECT EMPNO, FIRSTNME, LASTNAME
FROM   EMP.EMPLOYEE
WHERE  LASTNAME LIKE 'H%';

-- Employees in multiple departments using IN
SELECT EMPNO, LASTNAME, WORKDEPT
FROM   EMP.EMPLOYEE
WHERE  WORKDEPT IN ('A00', 'B01', 'C01');

You can combine multiple conditions using AND and OR. When mixing them, always use parentheses to make precedence explicit — DB2 evaluates AND before OR, and an unparenthesised mix of the two is a common source of logic bugs.

-- Managers in department A00 OR any employee earning over 90,000
SELECT EMPNO, LASTNAME, JOB, WORKDEPT, SALARY
FROM   EMP.EMPLOYEE
WHERE  (JOB = 'MANAGER' AND WORKDEPT = 'A00')
   OR  SALARY > 90000.00;

Sorting Results with ORDER BY

The ORDER BY clause controls the sequence of rows returned. Without it, DB2 makes no guarantee about output order — a point that surprises many developers coming from file-based COBOL thinking. On z/OS, a sequential VSAM file always returns records in physical order; a relational table has no such guarantee.

-- Sort employees by salary descending, then by last name ascending
SELECT EMPNO, LASTNAME, SALARY, WORKDEPT
FROM   EMP.EMPLOYEE
ORDER BY SALARY DESC,
         LASTNAME ASC;

You can reference columns by their position in the SELECT list (1-based) instead of by name, which is sometimes useful in ad hoc queries but should be avoided in stored procedures or application programs — column positions change when the SELECT list is modified.

-- ORDER BY using column position (position 3 = SALARY)
SELECT EMPNO, LASTNAME, SALARY
FROM   EMP.EMPLOYEE
ORDER BY 3 DESC;

When combined with FETCH FIRST (discussed next), ORDER BY plus FETCH FIRST is the standard DB2 pattern for "top N" queries.

Limiting Rows with FETCH FIRST

DB2 does not use MySQL's LIMIT keyword. The SQL standard mechanism — and the one DB2 supports — is FETCH FIRST n ROWS ONLY. This clause is especially important in OLTP systems where a runaway query could retrieve millions of rows and saturate the buffer pool.

-- Return only the top 5 highest-paid employees
SELECT EMPNO, LASTNAME, SALARY
FROM   EMP.EMPLOYEE
ORDER BY SALARY DESC
FETCH FIRST 5 ROWS ONLY;

DB2 also supports FETCH FIRST 1 ROW ONLY (singular) as valid syntax. On z/OS, when you know a query will return exactly one row, adding this clause can allow the optimizer to choose a more efficient access path.

-- Find the single highest-paid employee
SELECT EMPNO, LASTNAME, SALARY
FROM   EMP.EMPLOYEE
ORDER BY SALARY DESC
FETCH FIRST 1 ROW ONLY;

For pagination, DB2 11 for z/OS and DB2 LUW both support OFFSET n ROWS before the FETCH FIRST clause:

-- Skip the first 10 rows, then return the next 5 (page 3 of 5-per-page results)
SELECT EMPNO, LASTNAME, SALARY
FROM   EMP.EMPLOYEE
ORDER BY SALARY DESC
OFFSET 10 ROWS
FETCH FIRST 5 ROWS ONLY;

Column Aliases with AS

Column aliases rename output columns, making result sets more readable, especially when expressions are involved. DB2 supports the AS keyword or simply a space between the expression and the alias name.

SELECT EMPNO                        AS EMPLOYEE_NUMBER,
       FIRSTNME || ' ' || LASTNAME  AS FULL_NAME,
       SALARY / 12                  AS MONTHLY_SALARY,
       HIREDATE                     AS DATE_HIRED
FROM   EMP.EMPLOYEE
ORDER BY FULL_NAME;

Note that in DB2, you can reference a column alias in ORDER BY, but NOT in a WHERE clause within the same query block. The WHERE clause is evaluated before the SELECT projection, so the alias does not yet exist. Attempting to use an alias in WHERE will produce a SQL error.

Eliminating Duplicate Rows with DISTINCT

The DISTINCT keyword removes duplicate rows from the result. It applies to the entire selected row, not just one column. Use it when you want unique combinations of values across all selected columns.

-- Find all unique departments that have employees
SELECT DISTINCT WORKDEPT
FROM   EMP.EMPLOYEE
ORDER BY WORKDEPT;

-- Unique combinations of department and job title
SELECT DISTINCT WORKDEPT, JOB
FROM   EMP.EMPLOYEE
ORDER BY WORKDEPT, JOB;

Be aware that DISTINCT forces a sort or hash operation internally. On large tables, it carries a measurable CPU cost. If you know the data is already distinct because of a primary key or unique index, omit DISTINCT to avoid the unnecessary overhead.

Expressions and Computed Columns in SELECT

DB2 allows arithmetic expressions, string concatenation (using ||), and function calls directly inside the SELECT list. This is powerful for deriving calculated values without requiring application-side computation.

-- Calculate annual bonus as 10% of salary plus commission
SELECT EMPNO,
       LASTNAME,
       SALARY,
       COALESCE(COMM, 0)                          AS COMMISSION,
       SALARY + COALESCE(COMM, 0)                 AS TOTAL_COMP,
       (SALARY + COALESCE(COMM, 0)) * 0.10        AS BONUS_10PCT
FROM   EMP.EMPLOYEE
ORDER BY BONUS_10PCT DESC;

String concatenation uses the double-pipe || operator in DB2, not the + operator (which is reserved for numeric addition):

-- Concatenate first name, a space, and last name
SELECT EMPNO,
       FIRSTNME || ' ' || LASTNAME  AS FULL_NAME,
       UPPER(LASTNAME)              AS LAST_UPPER,
       LENGTH(LASTNAME)             AS NAME_LENGTH
FROM   EMP.EMPLOYEE;

DB2 Date and Time Functions in SELECT

DB2 provides special registers — CURRENT DATE, CURRENT TIME, and CURRENT TIMESTAMP — that return the current system date/time at the moment the query is bound or executed. These are used constantly in auditing, age calculations, and time-windowed queries.

-- Show current date and time alongside employee data
SELECT EMPNO,
       LASTNAME,
       HIREDATE,
       CURRENT DATE                              AS TODAY,
       CURRENT DATE - HIREDATE                   AS TENURE_DAYS,
       YEAR(CURRENT DATE) - YEAR(HIREDATE)       AS APPROX_YEARS
FROM   EMP.EMPLOYEE
ORDER BY HIREDATE;

DB2 date arithmetic returns a DECIMAL value representing a date duration. To get clean integer years of service, the TIMESTAMPDIFF function offers more precision:

-- Calculate exact years of service using TIMESTAMPDIFF
SELECT EMPNO,
       LASTNAME,
       HIREDATE,
       TIMESTAMPDIFF(256,
         CHAR(CURRENT TIMESTAMP - TIMESTAMP(HIREDATE, '00.00.00'))
       ) AS YEARS_OF_SERVICE
FROM   EMP.EMPLOYEE;

The magic number 256 in TIMESTAMPDIFF means "years". Other intervals: 16 = seconds, 32 = minutes, 64 = hours, 128 = days, 256 = years.

Selecting Across Multiple Schemas

In a DB2 environment — especially on z/OS where multiple subsystems and application areas share one database — data often lives in different schemas. You can query across schemas in a single SELECT by fully qualifying each table name.

-- Join EMPLOYEE table from EMP schema with DEPARTMENT from HR schema
SELECT E.EMPNO,
       E.LASTNAME,
       E.WORKDEPT,
       D.DEPTNAME,
       D.MGRNO
FROM   EMP.EMPLOYEE   E
JOIN   HR.DEPARTMENT  D
  ON   E.WORKDEPT = D.DEPTNO
ORDER BY D.DEPTNAME, E.LASTNAME;

You can also set a default schema for your session to avoid repeated qualification:

SET CURRENT SCHEMA = 'EMP';

-- Now you can omit the schema prefix
SELECT EMPNO, LASTNAME FROM EMPLOYEE;

On z/OS SPUFI sessions, the current schema is typically derived from your TSO user ID or a SET statement in the SQL script.

Handling NULL Values in SELECT

NULL in DB2 means "unknown" or "not applicable" — it is not the same as zero, an empty string, or blank. Arithmetic involving NULL produces NULL. Comparison to NULL using = always returns UNKNOWN, not TRUE or FALSE. This is the source of many subtle bugs in DB2 SQL.

The correct way to test for NULL is with IS NULL or IS NOT NULL:

-- Find employees with no commission (NULL commission)
SELECT EMPNO, LASTNAME, COMM
FROM   EMP.EMPLOYEE
WHERE  COMM IS NULL;

-- Find employees who do have a commission assigned
SELECT EMPNO, LASTNAME, COMM
FROM   EMP.EMPLOYEE
WHERE  COMM IS NOT NULL;

To substitute a safe default for NULL in output, use COALESCE() or VALUE() (VALUE is a DB2-specific synonym for COALESCE):

-- Display 0.00 instead of NULL for commission
SELECT EMPNO,
       LASTNAME,
       COALESCE(COMM, 0.00)  AS COMMISSION_SAFE
FROM   EMP.EMPLOYEE;

The NULLIF() function does the opposite — it returns NULL when two expressions are equal, which is useful for preventing division-by-zero errors:

-- Safe division: avoid divide-by-zero if BONUS column is 0
SELECT EMPNO,
       SALARY / NULLIF(BONUS, 0)  AS SALARY_PER_BONUS_UNIT
FROM   EMP.EMPLOYEE;

Practical Multi-Column Example: Employee Summary Report

Bringing everything together, here is a realistic employee summary query that a mainframe batch job or a reporting tool might execute:

-- Full employee summary with computed fields, NULL handling, and sorting
SELECT E.EMPNO                                      AS EMP_ID,
       FIRSTNME || ' ' || E.LASTNAME                AS FULL_NAME,
       D.DEPTNAME                                   AS DEPARTMENT,
       E.JOB                                        AS JOB_TITLE,
       E.SALARY                                     AS BASE_SALARY,
       COALESCE(E.COMM, 0.00)                       AS COMMISSION,
       E.SALARY + COALESCE(E.COMM, 0.00)            AS TOTAL_COMP,
       YEAR(CURRENT DATE) - YEAR(E.HIREDATE)        AS YRS_SERVICE,
       CASE
         WHEN E.SALARY > 80000 THEN 'SENIOR'
         WHEN E.SALARY > 50000 THEN 'MID'
         ELSE 'JUNIOR'
       END                                          AS GRADE
FROM   EMP.EMPLOYEE   E
JOIN   EMP.DEPARTMENT D
  ON   E.WORKDEPT = D.DEPTNO
WHERE  E.SALARY > 30000.00
  AND  D.DEPTNO <> 'X99'
ORDER BY D.DEPTNAME ASC,
         E.SALARY   DESC
FETCH FIRST 50 ROWS ONLY;

This single query demonstrates column aliasing, NULL coalescing, a CASE expression for derived grades, a JOIN to a second table, compound WHERE conditions, composite ORDER BY, and row limiting — all common patterns in real DB2 workloads.

Common Beginner Mistakes with DB2 SELECT

Mistake 1: Using = to compare NULL. WHERE COMM = NULL always returns no rows. Use WHERE COMM IS NULL instead.

Mistake 2: Forgetting the schema qualifier. On z/OS, SELECT * FROM EMPLOYEE will fail with "table not found" unless your session has a matching default schema. Always qualify: EMP.EMPLOYEE.

Mistake 3: Assuming ORDER BY is stable without a unique sort key. If two employees have the same salary, the ORDER BY SALARY result is non-deterministic between executions. Add a tiebreaker like ORDER BY SALARY DESC, EMPNO to get repeatable results.

Mistake 4: Using DISTINCT when it isn't needed. If the primary key is in the SELECT list, the result is already unique. Adding DISTINCT just forces the optimizer to perform unnecessary work.

Mistake 5: Reading millions of rows without FETCH FIRST. In a mainframe CICS transaction, a cursor that fetches all rows from a large table will hold locks, consume buffer pool pages, and potentially ABEND. Always code a row limit for interactive queries.

Mistake 6: Embedding literals that should be host variables. In embedded SQL (COBOL or PL/I), WHERE EMPNO = 'E001' forces a hard parse every time the value changes. Use host variables (:WS-EMPNO) to allow plan reuse and parameterized access paths.

Key Takeaways

The DB2 SELECT statement is far more than a simple data retrieval tool. By combining schema qualifiers, the WHERE clause, ORDER BY, FETCH FIRST, column aliases, expressions, NULL-safe functions like COALESCE, and DB2's built-in date registers, you can produce sophisticated, efficient queries that are ready for both ad hoc analysis and production COBOL programs. The examples in this guide, based on the IBM sample EMPLOYEE and DEPARTMENT tables, are patterns you will encounter repeatedly in real-world DB2 environments.

For a quick reference on all major DB2 SQL syntax, check out the DB2 Cheat Sheet. To go deeper with structured lessons on DB2 performance, package binding, and advanced SQL, explore the DB2 Mastery Course.