Comparison Operators FAQS

1. What are Oracle Comparison Operators?

• Used to compare two expressions or values in SQL.
• Evaluate conditions like equality, greater than, less than, etc.
• Essential for filtering and sorting data in SQL queries.

2. How do I compare NULL values in Oracle?

• NULL values cannot be compared directly using comparison operators like =, <>, etc. Instead, you need to use IS NULL or IS NOT NULL:

• To check for NULL:

SELECT * FROM employees WHERE commission_pct IS NULL;

• To check for non-NULL:

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

3. Can I use = to compare string values in Oracle?

• Yes, you can use the = operator to compare string values.
• However, string comparison is case-sensitive by default in Oracle:

SELECT * FROM employees WHERE last_name = 'Smith';

• This query will match only rows where the last_name is exactly "Smith" (with the same case)

4. What is the difference between <> and != in Oracle?

• Both <> and != are valid operators for "not equal to" in Oracle.
• There is no functional difference, and you can use either:

SELECT * FROM employees WHERE department_id <> 10;

SELECT * FROM employees WHERE department_id != 10;

• Both queries will yield the same result.

5. What does the BETWEEN operator do?

• The BETWEEN operator checks if a value falls within a specified range, including the boundary values.
• For example:

SELECT * FROM products WHERE price BETWEEN 50 AND 100;

• This will return all products whose price is between 50 and 100, inclusive.

6. How does the IN operator work in Oracle?

• The IN operator allows you to compare a column's value to a list of multiple values. It can also work with a subquery. Example:

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

• This will return all employees who belong to departments 10, 20, or 30.

7. What is the difference between ANY and ALL in Oracle?

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

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

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

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

8. What does the LIKE operator do?

• The LIKE operator is used for pattern matching in strings. Wildcards are used:

• % (percent) represents any sequence of characters (including no characters).
• _ (underscore) represents a single character.

Example:

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

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

9. Can I compare two subqueries in Oracle using comparison operators?

• Yes, you can compare the results of subqueries using comparison operators. Oracle allows single-row subqueries (e.g., =, >) and multi-row subqueries (e.g., IN, ANY, ALL):

-- Single-row subquery

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

 -- Multi-row subquery

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

10. What is the purpose of the NULLIF operator?

• The NULLIF function returns NULL if two values are equal; otherwise, it returns the first value. 
• This is useful for avoiding division by zero or handling specific value comparisons.

Example:

SELECT NULLIF(salary, 50000) FROM employees;

• If the salary is 50,000, the result will be NULL; otherwise, it will return the salary value.

11. What happens if I compare a NULL value using the = operator?

• Comparing a NULL value with = or any other comparison operator (e.g., >, <) will not return TRUE or FALSE. Instead, the result will be UNKNOWN.
• In SQL, NULL represents an unknown value, so any comparison with NULL returns UNKNOWN.

12. Can I use the IS NULL operator in a condition with other comparison operators?

• No, you cannot use IS NULL directly with other comparison operators (like =, >, etc.). You must use IS NULL or IS NOT NULL independently. Example:

SELECT * FROM employees WHERE commission_pct IS NULL;

13. What does the EXISTS operator do in a query?

• The EXISTS operator checks whether a subquery returns any rows.
• It returns TRUE if the subquery results in at least one row and FALSE if no rows are returned.

Example:

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

• This will return all employees who belong to a department.

14. What is the difference between IN and EXISTS?

• IN is used when you want to compare a value to a list of values returned by a subquery.
• EXISTS checks whether the subquery returns any rows, regardless of the actual values in the subquery.

Example with IN:

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

Example with EXISTS:

SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.location = 'New York');