Conditions and Expressions

A condition is comprised of one or more expressions along with one or more operators. Examples of expressions include:

•  Numbers such as 1,2,3
•  Columns such as cust_name
•  Literals such as (‘ABC’)
•  Functions UPPER (‘abc’)
•  List of expressions such as (1,2,3)
•  Subqueries

Examples of Operator include:

•  Arithmetic operators, such as +,-,* and /
• Comparison Operators, such as =,<,>=,!=, Like and In

Equality/Inequality Conditions

Most of the conditions that we use when constructing a WHERE clause will be equality conditions used to join data sets together or to isolate specific values. including:

s.supplier_id = p.supplier_id

s.name = 'Acme Industries'

supplier_id = (SELECT supplier_id  FROM supplier  WHERE name = 'Acme Industries')

In all three cases, we have a column expression followed by a comparison operator (=) followed by another expression. The conditions differ in the type of expression on the right side of the comparison operator. The first example compares one column to another, the second example compares a column to a literal, and the third example compares a column to the value returned by a subquery.

We can also build conditions that use the inequality comparison operator “!=”. Using the != operator rather than using NOT makes the query easier to understand and removes the need for the OR operator:

SELECT p.part_nbr, p.name, p.supplier_id, p.status, p.inventory_qty,  s.supplier_id, s.name

 FROM part p, supplier s

WHERE s.supplier_id = p.supplier_id  AND s.name != 'Acme Industries'  AND s.name != 'Tilton Enterprises';

While this is an improvement over the previous version, the next section shows an even cleaner way to represent the same logic.

Membership Conditions

Determining whether two expressions are identical, it is often useful to determine whether one expression can be found within a set of expressions. Using the IN operator, we can build conditions that will evaluate to TRUE if a given expression exists in a set of expressions:

s.name IN ('Acme Industries', 'Tilton Enterprises')

we may also add the NOT operator to determine whether an expression does not exist in a set of expressions:

s.name NOT IN ('Acme Industries', 'Tilton Enterprises')

SELECT part_nbr, name, supplier_id, status, inventory_qty

FROM part

WHERE  name NOT IN ('Acme Industries', 'Tilton Enterprises');

Range Conditions

Dealing with dates or numeric data, we may be look in whether a value falls within a specified range rather than whether it matches a specific value or exists in a finite set. For such cases, we may use the BETWEEN…AND operator, as in:

DELETE FROM cust_order 

WHERE order_dt BETWEEN '01-JUL-2001' AND '31-JUL-2001';

To determine whether a value lies outside a specific range, you can add the NOT operator:

SELECT order_nbr, cust_nbr, sale_price FROM cust_order WHERE sale_price NOT BETWEEN 1000 AND 10000;

When using BETWEEN, make sure the first value is the lowest of the two values provided. Ranges may also be specified using the operators <, >, <=, and >=, although doing so requires writing two conditions rather than one. The previous query could also be expressed as:

SELECT order_nbr, cust_nbr, sale_price FROM cust_order WHERE sale_price < 1000 OR sale_price > 10000;

Matching Conditions

When dealing with character data, there are some situations where you are looking for an exact string match, and others where a partial match is sufficient. For the latter case, we can use the LIKE operator along with one or more pattern-matching characters, as in:

DELETE FROM part 

WHERE part_nbr LIKE 'ABC%';

The pattern-matching character “%” matches strings of any length, so all of the following part numbers would be deleted: 'ABC', 'ABC-123', 'ABC9999999'. If you need finer control, you can use the underscore (_) pattern-matching character to match single characters, as in:

DELETE FROM part WHERE part_nbr LIKE '_B_';

For this pattern, any part number with exactly 3 characters with a B in the middle would be deleted. Both pattern-matching characters may be utilized in numerous combinations to find the desired data.