Now that we understand how conditions are grouped together and evaluated, let’s look at the different elements that make up a condition. A condition is comprised of one or more expressions along with one or more operators. Examples of expressions include:
Columns, such as s.supplier_id
Literals, such as ‘Acme Industries’
Functions, such as UPPER('abcd')
Lists of simple expressions, such as (1, 2, 3)
Examples of operators include:
Arithmetic operators, such as +, -, *, and /
Comparison operators, such as =, <, >=, !=, LIKE, and IN
The following sections explore many of the common condition types that use different combinations of the above expression and operator types.
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. We have already encountered these types of conditions numerous times in previous examples, 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 ...