DB2 - SQL Where Clause

The WHERE clause is used to filter records.

Retrieving just the data you want, involves specifying search criteria, also known as a filter condition. Within a SELECT statement, data is filtered by specifying search criteria in the WHERE clause.

Syntax:

SELECT column1, column2, ...
  FROM table_name
 WHERE condition;

WHERE clause is not only used in SELECT statement, it is also used in UPDATE, DELETE statement.

DB2 Database:

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

Example:

The below SQL statement retrieves two columns from the "Product" table, but instead of returning all rows, only rows with a SRP value of 75.00 are returned.

SELECT ProductID, ProductDesc
  FROM Product
 WHERE SRP = 75.00;

Result:

Numeric Fields vs Text Fields

SQL requires single quotes around text values (most database systems will also allow double quotes).. However, numeric fields should not be enclosed in quotes.

SELECT ProductID, ProductDesc, SRP
  FROM Product
 WHERE Category = 'Accessories';

Result:

WHERE Clause Operators:

SQL supports a whole range of conditional operators in the WHERE clause as listed.

Example 1:

To List all products that cost less than $10

SELECT ProductID, ProductDesc
  FROM Product
 WHERE SRP < 10;

Result:

Example 2:

To List all Category products other than 'Accessories'

SELECT ProductID, ProductDesc, SRP
  FROM Product
  WHERE Category <> 'Accessories';

Result:

Example 3:

To retrieve all products with a price between $5 and $10, including the specified start and end values.

SELECT ProductID, ProductDesc
  FROM Product
  WHERE SRP BETWEEN 5 AND 25;

Result:

Example 4:

To return a list of all products that have no Category(i.e. null value)

SELECT ProductID, ProductDesc, SRP
  FROM Product
 WHERE Category IS NULL;

Result:

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