Oracle SQL Operators and Conditions

SQL conditions in the WHERE clause

A condition is an expression in the WHERE clause that evaluates to either TRUE, FALSE, or UNKNOWN. Oracle returns a row only when the overall condition evaluates to TRUE.

A simple condition usually has:

• Left-hand expression (often a column)
• Comparison operator (for example, =, <>, >)
• Right-hand expression (literal, expression, or column)

The following figure shows examples of common operators in realistic business queries.

The rest of this lesson focuses on operators and conditions that sometimes cause confusion in real applications: pattern matching with LIKE, wildcards, IS NULL, and the NOT operator with grouped conditions.

1. LIKE operator for pattern matching

   The LIKE operator compares a character value to a pattern. Instead of matching the entire value exactly, you can use wildcards to match part of the value.

   Suppose you know a customer's last name begins with the letter L, but you are not sure how the rest of the name is spelled. To list all customers whose last names begin with L, you can write:

   

   This query returns any row where LASTNAME starts with L followed by zero or more characters, because of the % wildcard at the end of the pattern.

2. Wildcards used with LIKE

   The two wildcards available in Oracle for the LIKE operator are:

   • % – represents zero or more characters.
   • _ – represents exactly one character.

   You can use these wildcards multiple times in the same expression and even build patterns dynamically from column values.

   For example, to find customers whose first and last names start with the same letter (such as Lois Lane), you could write:

   
   SELECT FIRSTNAME, LASTNAME
   FROM   CUSTOMER
   WHERE  LASTNAME LIKE SUBSTR(FIRSTNAME, 1, 1) || '%';
      

   Here, the SUBSTR function returns the first character of the first name, and the pattern '%' is concatenated to create a value such as 'S%'. The LIKE operator then matches any last name that begins with that same letter.

   

3. IS NULL and IS NOT NULL

   A NULL represents an unknown or missing value. It is not the same as zero, a blank string, or a default value. To test for nulls, you must use the IS NULL and IS NOT NULL conditions.

   To find all rows where PACKAGE_ID is null:

   
   PACKAGE_ID IS NULL
      

   Comparisons like P.LAST_UPDATE_DATE = PCL.LAST_UPDATE_DATETIME will return FALSE when both values are null, because NULL = NULL is not considered true in SQL. If you want to treat “both null” as equal, you must test explicitly:

   
   (P.LAST_UPDATE_DATE = PCL.LAST_UPDATE_DATETIME
    OR
    (P.LAST_UPDATE_DATE IS NULL
     AND PCL.LAST_UPDATE_DATETIME IS NULL))
      

   You can also use IS NOT NULL to ensure that a column has a value. For example, to find rows that have a last update date:

   
   P.LAST_UPDATE_DATE IS NOT NULL
      

4. NOT operator

   The NOT operator reverses the truth value of a condition. It can be applied to a single comparison or to a grouped set of conditions enclosed in parentheses.

   For a single comparison, you can write:

   
   NOT TOTAL_ITEM_AMOUNT > 30
      

   This condition is logically equivalent to TOTAL_ITEM_AMOUNT <= 30, but you will often see NOT used in more complex expressions.

5. Grouping comparisons with NOT

   Parentheses let you group several comparisons together and then apply NOT to the entire group. This is standard SQL behavior and is especially useful when your business rules are easier to state in the negative.

   For example, the following condition finds rows where the total of items is greater than 30 and the total sale amount is less than 45:

   
   TOTAL_ITEM_AMOUNT > 30
   AND TOTAL_SALE_AMOUNT < 45
      

   To find all rows that do not match those criteria, you can negate the whole expression:

   
   NOT (TOTAL_ITEM_AMOUNT > 30
        AND TOTAL_SALE_AMOUNT < 45)
      

   By grouping the conditions in parentheses and placing NOT in front, you reverse the result of the combined comparison. Parentheses also make the intended logic clear to anyone who reads or maintains the query.