SQL WHERE || DB2 for i SQL

Using WHERE Clause in SELECT statement in DB2 for i SQL
Using WHERE Clause in SELECT statement in DB2 for i SQL, Where clause, predicate, sql, db2 for i
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'


    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


  • 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'


    which is equivalent to



    EMPID       EMPNAME               MANAGERID 
    E2          EMPNAME2              M2        

    Post a Comment

    © AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain