DB2 - SQL AND, OR and NOT Operators

For a greater degree of filter control, DB2 SQL lets you specify multiple WHERE clauses.

Operator: A special keyword used to join or change clauses within a WHERE clause. This is also known as logical operators.

The AND and OR operators are used to filter records based on more than one condition.

1. The AND operator displays a record if all the conditions separated by AND are TRUE.
2. The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

Syntax:

SELECT column1, column2, ...
  FROM table_name
  WHERE condition1 AND/OR condition2 AND/OR condition3 ...;

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

Example 1: Using the AND Operator:

To filter by more than one column, we use the AND operator to append conditions to our WHERE clause. This is a keyword used in a WHERE clause to specify that only rows matching all the specified conditions should be retrieved. “AND” instructs the DB2 to return only rows that meet all the conditions specified.

SELECT ProductId, ProductName, Price
    FROM Product
    WHERE Category = 'Accessories' AND Price > 10;

This SQL statement retrieves the ProductId, ProductName and Price for all products of Category 'Accessories' and the price is greater than 10.

The WHERE clause in this SELECT statement is made up of two conditions, and the keyword AND is used to join them.

Result:

Example 2: Using the OR Operator

The OR operator is exactly the opposite of AND.

The OR operator instructs the DB2 to retrieve rows that match either one condition or both.

SELECT ProductId, ProductName, Price
  FROM Product
  WHERE Category = 'Accessories' OR Price > 10;

This SQL statement retrieves the ProductId, ProductName and Price for any products of either the Category = 'Accessories' or Price > 10

Result:

Order of Evaluation:

WHERE clauses can contain any number of AND and OR operators.

SQL processes AND operators before OR operators.

Example 3:

To get a list of all ProductId for the Category 'Accessories' and 'Components' with Price 10 or more.

SELECT ProductId
    FROM Product
    WHERE (Category = 'Accessories' OR Category = 'Components') AND Price >= 10;

If you do not use parentheses, you will not get desired output.

Whenever you write WHERE clauses that use both AND and OR operators, use parentheses to explicitly group operators.

Result:

Example 4: Using the NOT Operator

The following SQL statement selects all fields from "Product" where Category is NOT 'Accessories'.

SELECT * FROM Product
    WHERE NOT Category = 'Accessories';

Result:

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!