Db2 OR

Summary: in this tutorial, you will learn how to use the Db2 OR operator to query rows that satisfy either or both predicates of the search condition.

Introduction Db2 OR operator

The OR operator is a logical operator that combines two Boolean expressions or predicates. the OR operator is often used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to specify a search condition for rows to be selected, updated, and deleted.

Here is the syntax of the OR operator:

boolean_expression1 OR boolean_expression2
Code language: SQL (Structured Query Language) (sql)

In this syntax, the boolean_expression1 and boolean_expression2 are the Boolean expressions that evaluate to true, false, and unknown.

The following table shows the result when combining true, false, and unknown values using the OR operator:

TRUEFALSEUNKNOWN
TRUETRUETRUETRUE
FALSETRUEFALSEUNKNOWN
UNKNOWNTRUEUNKNOWNUNKNOWN

If you use both OR and AND operators in an expression, Db2 always evaluates the AND operator first. To change the order of evaluation, you can use the parentheses.

To negate the OR operator, you use the NOT operator as follows:

NOT (boolean_expression1 OR boolean_expression2)
Code language: SQL (Structured Query Language) (sql)

Db2 OR operator examples

Let’s use the books table from the sample database to demonstrate the OR operator.

1) Db2 OR operator example

This example uses the OR operator to find the books that have the number of pages 500 or 1,000:

SELECT title, total_pages FROM books WHERE total_pages = 500 OR total_pages = 1000 ORDER BY total_pages;
Code language: SQL (Structured Query Language) (sql)

The query scans every row and returns the rows whose value in the total_pages column is 500 or 1000.

Here is the result set:

Db2 OR example

Note that you can use the IN operator to achieve the same result:

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

2) Using multiple Db2 OR operators example

This example uses two OR operators to find books whose ratings are 5 and the number of pages is 500 or 1000:

SELECT title, total_pages, rating FROM books WHERE total_pages = 500 OR total_pages = 1000 OR rating = 5 ORDER BY total_pages;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Db2 OR multiple OR operators example

3) Using Db2 OR operator with parentheses

If you use the OR and AND operators with more than two conditions, you can use parentheses to explicitly specify the evaluation order.

The following example uses both OR and AND operators to find books that satisfy either of the following conditions:

  • The number of pages is greater than 800 and less than 1,000
  • The rating is greater than 4.7

Here is the query:

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

The following picture shows the partial output:

Db2 OR with AND example

4) Using Db2 NOT OR operator example

This example uses the NOT OR operator to find books that have the number of pages greater than or equal 1000 and rating less than or equal 4:

SELECT title, rating, total_pages FROM books WHERE NOT (rating > 4 OR total_pages < 1000) ORDER BY rating desc, total_pages desc;
Code language: SQL (Structured Query Language) (sql)

Here is the partial result set:

Db2 NOT OR example

Note that based on the De Morgan’s laws, NOT (A OR B) is equivalent to (NOT A AND NOT B). In this case, the following conditions are equivalent:

NOT (rating > 4 OR total_pages < 1000)
Code language: SQL (Structured Query Language) (sql)

and

rating <= 4 AND total_pages >= 1000)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 OR operator to query rows that satisfy either or both predicates of the search condition.

Was this tutorial helpful ?