Db2 WHERE

Summary: in this tutorial, you will learn how to use the Db2 WHERE clause to specify the search condition for rows returned by a query.

Introduction to Db2 WHERE clause

The WHERE clause is an optional clause of the SELECT statement. The WHERE clause specifies the search condition for the rows returned by the SELECT statement.

The following shows the syntax of the WHERE clause in the SELECT statement:

SELECT
    select_list
FROM
    table_name
WHERE
    search_condition
ORDER BY
    sort_expression;
Code language: SQL (Structured Query Language) (sql)

In this syntax, the search_condition like a filter that defines a condition for the returned rows. The rows that cause the search_condition evaluate to true will be included in the result set.

The search_condition may consist of one or many logical expressions that evaluate to true, false, or unknown. The logical expressions in the WHERE clause are also known as predicates.

The WHERE clause appears after the FROM clause and before the ORDER BY clause. When evaluating the SELECT statement, Db2 evaluates the clauses in the following sequence: FROM, WHERE, SELECT, and ORDER BY.

Besides the SELECT statement, the WHERE clause are used in the UPDATE or DELETE statement to specify rows to be updated or deleted.

Db2 WHERE clause examples

We’re going to use the books table from the sample database to demonstrate the WHERE clause:

1) Using simple equality operator in Db2 WHERE clause example

This query uses the WHERE clause to find books whose ratings are 4:

SELECT    
    title, 
    total_pages, 
    rating, 
    published_date
FROM    
    books
WHERE 
    rating = 4
ORDER BY 
    title;
Code language: SQL (Structured Query Language) (sql)

Here are the output:

2) Using comparison operators in the Db2 WHERE clause example

The following query uses the WHERE clause to return books whose ratings are greater than 4.7 and less than or equal to 5:

SELECT    
    title, 
    total_pages, 
    rating, 
    published_date
FROM    
    books
WHERE rating > 4.7
        AND rating <= 5
ORDER BY 
    rating, 
    title;
Code language: SQL (Structured Query Language) (sql)

The following picture shows the result set:

3) Using Db2 WHERE clause to find rows with the value between two values

The following statement uses the BETWEEN operator in the WHERE clause to find books whose published dates are between Jan 01, 2018 and December 31, 2018:

SELECT    
    title, 
    total_pages, 
    rating, 
    published_date
FROM    
    books
WHERE 
    published_date BETWEEN '2018-01-01' AND '2018-12-31'
ORDER BY 
    published_date;
Code language: SQL (Structured Query Language) (sql)

4) Using Db2 WHERE clause to find rows that have a value in a list of values

The following example uses the IN operator in the WHERE clause to find books whose rating is 4 or 5

SELECT    
    title, 
    total_pages, 
    rating 
FROM    
    books
WHERE rating IN (4, 5)
ORDER BY 
    rating;
Code language: SQL (Structured Query Language) (sql)

5) Using Db2 WHERE clause to find rows whose values contain a string

This example uses the LIKE operator in the WHERE clause to find books whose title contains the string 'SQL':

SELECT    
    title, 
    total_pages, 
    rating, 
    published_date
FROM    
    books
WHERE title LIKE '%SQL%'
ORDER BY 
    title;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 WHERE clause to specify the search condition for rows returned by a query.