SQL WHERE || DB2 for i SQL

Using WHERE Clause in SELECT statement in DB2 for i SQL

The WHERE clause can be used to specify the search condition that identifies the row or rows that we want to retrieve, update or delete.

A search condition consists of one or more predicates. A predicate specifies a test that we want SQL to apply to specific row/rows of the table.

In the below query empid = 'E1' is a predicate where empid and 'E1' are expressions and the equal(=) sign is a comparison operator.

Select * from PF1 where empid = 'E1'

Output

EMPID       EMPNAME               MANAGERID
E1          EMPNAME1              M1       

Character values are enclosed in apostrophes(') and numeric values are not.

Expressions in the WHERE clause

It is that we want to compare to something else. We can specify following expressions.

Column Name

Where empid = 'E1'

Constant

WHERE 'E1' = EMPID

Host variable

WHERE empid = :empid

Global variable

Function

Special register

Where empName = User

Scalar FullSelect

Another Expression

NULL

Where empName is NULL

Note:

A search condition can specify many predicates separated by AND and OR. The search condition is evaluated as TRUE or FALSE against a row in the table.

Comparison operators used with WHERE clause

Supported SQL comparison operators.

Equal to (=)

Less than (<)

Greater than (>)

Not equal to (<> or ¬= or !=)

Less than or equal to (or not greater than) (<= or ¬> or !>)

Greater than or equal to (or not less than) (> = or ¬< or !<)

NOT keyword used with WHERE clause

We can precede a predicate with the NOT keyword to specify that we want the opposite of the predicate value i.e. TRUE if the predicate is FALSE. NOT only applies to the predicate it precede, not to all the predicates in the WHERE clause.

Therefore, if we want those employees whose employee id is not 'E1'

SELECT * FROM PF1 WHERE NOT EMPID = 'E1'

which is equivalent to

SELECT * FROM PF1 WHERE NOT EMPID <> 'E1'

Output

EMPID       EMPNAME               MANAGERID 
E2          EMPNAME2              M2