Types of SQL filter conditions
Table 12-1 describes the types of SQL filter conditions and provides examples of filter conditions that are used in WHERE clauses.
Table 12-1 Examples of filter conditions in the WHERE clause
Type of filter condition
Description
Examples of WHERE...
Comparison
Compares the value of one expression to the value of another expression.
quantity = 10
custName = 'Acme Inc.'
custName > 'P'
custState <> 'CA'
orderDate > {d '2005-06-30'}
Range
Tests whether the value of an expression falls within a range of values. The test includes the endpoints of the range.
price BETWEEN 1000 AND 2000
custName BETWEEN 'E' AND 'K'
orderDate BETWEEN
{d '2013-01-01'}
AND {d '2013-06-30'}
Membership
Tests whether the value of an expression matches one value in a set of values.
officeCode IN (101,103,104)
itemType IN ('sofa', 'loveseat', 'endtable', 'clubchair')
orderDate IN
({d '2013-10-10'},
{d '2013-10-17'})
Pattern-matching
Tests whether the value of a string field matches a specified pattern.
custName LIKE 'Smith%'
(% matches zero or more characters)
custName LIKE 'Smiths_n'
(_ matches one character)
custState NOT LIKE 'CA%'
Null value
Tests whether a field has a null, or missing, value.
manager IS NULL
shipDate IS NULL
shipDate IS NOT NULL
SQL provides many other operators and options that you can use to create more complex search conditions. For more information about the WHERE clause, see the SQL documentation for your JDBC database.