Module 5: DB2 SQL SELECT and Filtering

Comparison Operators

Comparison operators are the building blocks of every WHERE clause. They compare a column with a value (or with another column) and produce true, false, or unknown.

The six comparison operators

= equal to
<> not equal to
< less than
<= less than or equal to
> greater than
>= greater than or equal to
Every comparison returns true, false, or unknown (when NULL is involved).
Only true rows survive the WHERE clause.

Comparing numbers and dates

Numbers compare by value: SALARY >= 40000 is simple arithmetic.
Dates compare chronologically when written as 'yyyy-mm-dd': HIREDATE < '1970-01-01'.
Never compare a date column with a non-date string like '01-JAN-70' - use the ISO format.
Comparing a number column with a quoted value ('35000') forces a conversion - avoid it.

Comparing character data

Character comparisons use the collating sequence (EBCDIC on the mainframe).
In EBCDIC, digits sort before uppercase letters, and uppercase before lowercase.
Shorter values are padded with blanks: 'D1' equals 'D1 ' in a CHAR(4) column.
Comparisons are case-sensitive - 'haas' is not equal to 'HAAS'.

Example

SELECT LASTNAME, HIREDATE, SALARY FROM EMP WHERE HIREDATE < '1970-01-01' AND SALARY >= 35000; -- Result: hired before 1970 and earning at least 35000 LASTNAME HIREDATE SALARY -------- ---------- ------ HAAS 1965-01-01 52750 GEYER 1949-08-17 40175

Backward