How many Comparison Operators are there in Oracle?

Comparison Operators in Oracle

Powerful tools for accessing and modifying data contained in tables are offered by the Oracle Structured Query Language (SQL), and essential elements for filtering results are Oracle Comparison Operators, sometimes referred to as logical operators or relational operators. These operators, which are usually used in the WHERE clause of a SELECT statement to establish qualifiers on the data being selected, are crucial for crafting relevant queries.

A comparison condition usually compares two expressions and returns TRUE, FALSE, or NULL (UNKNOWN) as the result. Letters or numbers that relate to literal constants or column values can be used as operands. Priority determines how Oracle SQL examines the operators; operators with a higher precedence are assessed before those with a lower precedence.

Fundamental Comparison Operators

The most basic comparison operators test for equality or inequality between values.

Numerous data kinds, such as dates, characters, and numbers, can be reliably handled by these operators. A higher value is seen as greater for numerical values. Determining linguistic or binary sorting rules and deciding whether to employ blank-padded or nonpadded comparison semantics are all part of the comparison process for character data. Nonpadded comparison semantics are used by Oracle for VARCHAR2 and NVARCHAR2 types.

Advanced Comparison Operators

Oracle offers operators for null testing, pattern matching, range checking, and list membership in addition to basic equality and relational tests.

• BETWEEN: For range searches, BETWEEN is used to verify that a value is larger than or equal to the lower bound and less than or equal to the upper bound (x BETWEEN a AND b is equivalent to x >= a AND x <= b). There is no logical negation between them.
• IN: Verifies whether a value appears in a list of parameters (e.g., Section IN ('A', 'C', 'F')). This is helpful for testing against a list of literal values or values that a subquery has produced. It is not in the negation.
• LIKE: Compares pattern information to character data. This makes use of two main wildcards: the underscore (_), which matches any single character, and the percent sign (%), which matches any string of zero or more characters. In most cases, pattern matching is case-sensitive unless configuration is altered. For instance, features beginning with ‘Mo’ would be found by using Feature LIKE 'Mo%'.
• IS NULL: This is the only way to check for null values, or empty values. Keep in mind that a null value is just empty; it is not zero. When testing for nulls, this is the only condition to apply because using = or != to compare a column to NULL will yield UNKNOWN.

By creating a sample table called EMPLOYEES_COMP and adding some values, we will illustrate these operators.

Create Table Statement

ID (NUMBER), Last Name (VARCHAR2), Salary (NUMBER), and Department (VARCHAR2) are the columns in the table we define:

CREATE TABLE EMPLOYEES_COMP (
    EMP_ID NUMBER(4) NOT NULL,
    LAST_NAME VARCHAR2(30),
    SALARY NUMBER(8,2),
    DEPARTMENT VARCHAR2(10)
);

Table created.

Insert Values

After that, we add example data. While numbers stand alone, character strings must be surrounded by single quote marks. Take note that the pay of employee 1006 is NULL:

INSERT INTO EMPLOYEES_COMP VALUES (1001, 'SMITH', 50000.00, 'SALES');
INSERT INTO EMPLOYEES_COMP VALUES (1002, 'ALLEN', 30000.00, 'SALES');
INSERT INTO EMPLOYEES_COMP VALUES (1003, 'JONES', 65000.50, 'IT');
INSERT INTO EMPLOYEES_COMP VALUES (1004, 'BLAKE', 50000.00, 'IT');
INSERT INTO EMPLOYEES_COMP VALUES (1005, 'CLARK', 45000.00, 'HR');
INSERT INTO EMPLOYEES_COMP VALUES (1006, 'ADAMS', NULL, 'HR');
COMMIT;

6 rows created.
Commit complete.

Comparison Operator

The WHERE clause and the SELECT statement are used to filter data according to comparison requirements.

Equality (=) and Greater Than (>)

Retrieve employees who earn more than $50,000:

SELECT EMP_ID, LAST_NAME, SALARY
FROM EMPLOYEES_COMP
WHERE SALARY > 50000;

Output:

EMP_ID LAST_NAME       SALARY
------ --------------- ----------
1003   JONES           65000.50

Inequality (!= or <>)

Retrieve employees not in the ‘SALES’ department:

SELECT EMP_ID, LAST_NAME, DEPARTMENT
FROM EMPLOYEES_COMP
WHERE DEPARTMENT != 'SALES'
ORDER BY EMP_ID;

Output:

EMP_ID LAST_NAME       DEPARTMENT
------ --------------- ----------
1003   JONES           IT
1004   BLAKE           IT
1005   CLARK           HR
1006   ADAMS           HR

Range (BETWEEN)

Retrieve employees whose salary is between $40,000 and $55,000 inclusive:

SELECT EMP_ID, LAST_NAME, SALARY
FROM EMPLOYEES_COMP
WHERE SALARY BETWEEN 40000 AND 55000;

Output:

EMP_ID LAST_NAME       SALARY
------ --------------- ----------
1001   SMITH           50000.00
1004   BLAKE           50000.00
1005   CLARK           45000.00

List Membership (IN)

Retrieve employees belonging to ‘HR’ or ‘IT’ departments:

SELECT EMP_ID, LAST_NAME, DEPARTMENT
FROM EMPLOYEES_COMP
WHERE DEPARTMENT IN ('HR', 'IT')
ORDER BY EMP_ID;

Output:

EMP_ID LAST_NAME       DEPARTMENT
------ --------------- ----------
1003   JONES           IT
1004   BLAKE           IT
1005   CLARK           HR
1006   ADAMS           HR

Pattern Matching (LIKE)

Retrieve employees whose last name contains the letter ‘L’:

SELECT EMP_ID, LAST_NAME
FROM EMPLOYEES_COMP
WHERE LAST_NAME LIKE '%L%';

Output:

EMP_ID LAST_NAME
------ ---------------
1002   ALLEN
1004   BLAKE
1005   CLARK

Null Condition (IS NULL)

Retrieve employees with an unknown (NULL) salary:

SELECT EMP_ID, LAST_NAME, SALARY
FROM EMPLOYEES_COMP
WHERE SALARY IS NULL;

Output:

EMP_ID LAST_NAME       SALARY
------ --------------- ----------
1006   ADAMS

Context and Insights on Comparison Rules

The type of data being compared has a significant impact on how Oracle handles it. In terms of dates, a later date is regarded as superior to an earlier one. The comparison for character values is contingent upon whether the data type is variable-length (VARCHAR2) or fixed-length (CHAR).

• CHAR (Fixed-Length): Blank-padded comparison semantics are used in CHAR (Fixed-Length) comparisons. Oracle matches the longer length before comparison by padding the shorter value with blanks if the lengths of the values disagree. If the amount of trailing blanks is the only difference between two CHAR values, then they are identical.
• VARCHAR2 (Variable-Length): Nonpadded comparison semantics are used in comparisons using VARCHAR2 (Variable-Length). Two values of differing lengths are regarded as bigger if they are identical until the end of the shorter one. VARCHAR2 is often advised for character string fields unless a fixed length is specifically needed.

Additionally, the IS NULL condition is necessary for accurate testing of unusual values like NULL because employing equality operators with NULL yields an unknown result. In Oracle Database 10g and later, Oracle also allows more complex comparison ideas, such comparing collection types (nested tables and varrays) using equality operators or comparing user-defined object types using functions like MAP or ORDER methods.