What Is Comparison Operators In Oracle?

In Oracle, comparison operators are used to compare two values, and they return a Boolean result (TRUE, FALSE, or NULL). These operators are essential in filtering and controlling the flow of queries, especially in SQL statements like SELECT, UPDATE, DELETE, and WHERE clauses. Here are the common comparison operators in Oracle:

1. = (Equal to)
   • Compares if two values are equal.
   • Example:

     SELECT * FROM employees WHERE salary = 50000;
     

2. != or <> (Not equal to)
   • Compares if two values are not equal.
   • Example:

     SELECT * FROM employees WHERE salary != 50000;
     

3. > (Greater than)
   • Compares if the left value is greater than the right value.
   • Example:

     SELECT * FROM employees WHERE salary > 50000;
     

4. < (Less than)
   • Compares if the left value is less than the right value.
   • Example:

     SELECT * FROM employees WHERE salary < 50000;
     

5. >= (Greater than or equal to)
   • Compares if the left value is greater than or equal to the right value.
   • Example:

     SELECT * FROM employees WHERE salary >= 50000;
     

6. <= (Less than or equal to)
   • Compares if the left value is less than or equal to the right value.
   • Example:

     SELECT * FROM employees WHERE salary <= 50000;
     

7. IS NULL (Null check)
   • Checks if a value is NULL (unknown or missing).
   • Example:

     SELECT * FROM employees WHERE commission_pct IS NULL;
     

8. IS NOT NULL (Not null check)
   • Checks if a value is not NULL.
   • Example:

     SELECT * FROM employees WHERE commission_pct IS NOT NULL;
     

9. BETWEEN (Range check)
   • Checks if a value is within a specified range (inclusive).
   • Example:

     SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
     

10. IN (Match against a list)

• Checks if a value matches any of the values in a list.
• Example:

  SELECT * FROM employees WHERE department_id IN (10, 20, 30);
  

11. LIKE (Pattern matching)

• Checks if a value matches a specified pattern (using wildcards).
• Example:

  SELECT * FROM employees WHERE first_name LIKE 'J%';
  

12. NOT LIKE (Pattern mismatch)

• Checks if a value does not match a specified pattern.
• Example:

  SELECT * FROM employees WHERE first_name NOT LIKE 'J%';
  

These operators are typically used in the WHERE clause to filter rows based on conditions or in other clauses like HAVING, or in functions and expressions.