SQL BETWEEN || DB2 for i SQL

BETWEEN AND predicate in Search condition in DB2 for i SQL

We can use various predicates in our WHERE clause condition such as BETWEEN, IN, EXISTS, IS NULL, LIKE apart from basic comparison predicates such as =, >,< etc.

We can also use multiple conditions in WHERE clause.

A search condition can contain a full select too.

BETWEEN/AND predicate

It is used to get the rows that falls between two values. For Example, refer to the Customer table and find out all the customer ids between 2 and 4. This will list out 2 and 4 boundary ids as well which means it in Inclusive.

SELECT * FROM customer WHERE CUSTID between 2 and 4

The above query can be written as below as well.

SELECT * FROM customer WHERE CUSTID >= 2 and  custid <= 4

Output:

CUSTID   CUSTNAME              CUSTCITY              CUSTSTATE             CUSTCOUNTRY
     2   Anil                  PATNA                 BIHAR                 INDIA      
     3   Dhanraj               HISAR                 HARYANA               INDIA      
     4   Udeep                 KOCHI                 KERELA                INDIA