Mastering Oracle SQL

Conditions and Expressions

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:

• Numbers

• 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)

• Subqueries

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.

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. 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 ...