Comparison Operators

In Oracle, comparison operators are used to compare two values or expressions in a SQL query. These operators return either TRUE, FALSE, or UNKNOWN depending on the comparison result. They are widely used in SQL conditions, especially in the WHERE clause, to filter data.

Here's a detailed breakdown of the common comparison operators in Oracle:

1. Equal to (=)

The equal-to operator is used to compare two values or expressions to check if they are equal.

Syntax:

SELECT * FROM employees WHERE salary = 50000;

This will retrieve all rows from the employees table where the salary is exactly 50,000.

Important Notes:

• It is case-sensitive for strings.
• NULL values cannot be directly compared with =. To check for NULL, use the IS NULL operator.

2. Not equal to (<> or !=)

The not-equal-to operator checks if two values or expressions are not equal to each other.

Syntax:

SELECT * FROM employees WHERE department_id <> 10;

This will retrieve all employees whose department ID is not 10.

Important Notes:

• Oracle supports both <> and != as valid syntax for "not equal to".
• Similar to the = operator, it cannot be used to directly compare NULL values. Use IS NOT NULL to handle NULL values.

3. Greater than (>)

The greater-than operator compares two values and returns true if the first value is greater than the second.

Syntax:

SELECT * FROM products WHERE price > 100;

This will return all products with a price greater than 100.

4. Greater than or equal to (>=)

This operator checks if the first value is greater than or equal to the second value.

Syntax:

SELECT * FROM employees WHERE hire_date >= TO_DATE('2020-01-01', 'YYYY-MM-DD');

This retrieves all employees hired on or after January 1, 2020.

5. Less than (<)

The less-than operator compares two values and returns true if the first value is less than the second.

Syntax:

SELECT * FROM products WHERE price < 50;

This will retrieve all products with a price less than 50.

6. Less than or equal to (<=)

The less-than or equal-to operator compares two values and returns true if the first value is less than or equal to the second.

Syntax:

SELECT * FROM employees WHERE salary <= 60000;

This will return employees whose salary is less than or equal to 60,000.

7. BETWEEN

The BETWEEN operator is used to filter results within a range. It is inclusive, meaning the boundary values are considered in the result.

Syntax:

SELECT * FROM products WHERE price BETWEEN 50 AND 100;

This will return all products where the price is between 50 and 100 (inclusive).

Important Notes:

• It works with numerical, date, and string values.
• The range is inclusive of the start and end values.

8. IN

The IN operator is used to compare a value to a set of multiple values. It simplifies multiple OR conditions.

Syntax:

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

This retrieves employees who belong to departments 10, 20, or 30.

Important Notes:

• The set of values can also be a subquery:

·        SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

9. LIKE

The LIKE operator is used to compare a string with a pattern. It is often used for partial matching.

Syntax:

SELECT * FROM employees WHERE last_name LIKE 'S%';

This will return all employees whose last name starts with "S".

Pattern Matching with Wildcards:

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

Example:

SELECT * FROM employees WHERE last_name LIKE '_ohn';

This will match "John", "Johnston", etc., but not "Jon".

10. IS NULL

The IS NULL operator is used to check if a value is NULL.

Syntax:

SELECT * FROM employees WHERE commission_pct IS NULL;

This will return employees who do not have a commission percentage assigned.

11. IS NOT NULL

The IS NOT NULL operator checks if a value is not NULL.

Syntax:

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

This will retrieve employees who have a commission percentage assigned.

12. NULLIF

The NULLIF function returns NULL if two values are equal; otherwise, it returns the first value.

Syntax:

SELECT NULLIF(salary, 50000) FROM employees;

This returns NULL if the employee's salary is 50,000; otherwise, it returns the salary value.

13. EXISTS

The EXISTS operator is used with subqueries to check if the subquery returns any rows. It returns TRUE if the subquery returns one or more rows, and FALSE if it does not.

Syntax:

SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id);

This will return all employees that belong to a department.

14. SINGLE ROW vs MULTI-ROW Comparisons

Some comparison operators are used in conjunction with single-row or multi-row subqueries.

• Single-row subquery (e.g., =, <, >, <=, >=, <>): These operators compare a value with the result of a single row returned by the subquery.

Example:

SELECT * FROM employees WHERE salary > (SELECT MAX(salary) FROM employees WHERE department_id = 10);

• Multi-row subquery (e.g., IN, ANY, ALL): These operators compare a value to multiple values returned by the subquery.

Example:

SELECT * FROM employees WHERE salary IN (SELECT salary FROM employees WHERE department_id = 10);

15. ANY and ALL

• ANY: Compares the value to any value in a list or subquery. It returns TRUE if the comparison is true for at least one value.

Example:

SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 10);

• ALL: Compares the value to all values in a list or subquery. It returns TRUE if the comparison is true for every value.

Example:

SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);

 

Summary of Oracle Comparison Operators:

Operator	Description
=	Equal to
<>, !=	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
BETWEEN	Range between two values
IN	Matches any value from a list or subquery
LIKE	Pattern matching for string values
IS NULL	Checks for NULL values
IS NOT NULL	Checks for non-NULL values
NULLIF	Returns NULL if two values are equal
EXISTS	Checks if a subquery returns rows
ANY	Compares with any value from a subquery
ALL	Compares with all values from a subquery

These operators are essential for building powerful and flexible SQL queries in Oracle databases. Understanding how to use them effectively can help you create more efficient queries and better filter the data according to your needs.