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.

Db2 WHERE clause Evaluation Order

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.

Was this tutorial helpful ?