Using Comparison Operators in Oracle

This Oracle tutorial explores all of the evaluation operators used in Oracle to take a look at for equality and inequality, as nicely as the more advanced operators.

Description

Comparison operators are used in the WHERE clause to decide which archives to select. Here is a list of the evaluation operators that you can use in Oracle/PLSQL:

Comparison Operator Description = Equal <> Not Equal != Not Equal > Greater Than >= Greater Than or Equal < Less Than <= Less Than or Equal IN ( ) Matches a price in a list NOT Negates a condition BETWEEN Within a vary (inclusive) IS NULL NULL value IS NOT NULL Non-NULL value LIKE Pattern matching with p.c and _ REGEXP_LIKE Pattern matching with normal expressions EXISTS Condition is met if subquery returns at least one row

Some of these operators are fairly straight ahead and others are extra complicated. Let’s begin by means of reviewing the less difficult assessment operators in Oracle.

Example – Equality Operator

In Oracle/PLSQL, you can use the = operator to check for equality in a query.

For example:

SELECT *
FROM customers
WHERE last_name = 'Anderson';

In this example, the SELECT assertion above would return all rows from the customers desk the place the last_name is equal to Anderson.

Example – Inequality Operator

In Oracle/PLSQL, you can use the <> or != operators to check for inequality in a query.

For example, we could test for inequality the use of the <> operator, as follows:

SELECT *
FROM customers
WHERE last_name <> 'Anderson';

In this example, the SELECT assertion would return all rows from the customers table the place the last_name is not equal to Anderson.

Or you could also write this question using the != operator, as follows:

SELECT *
FROM customers
WHERE last_name != 'Anderson';

Both of these queries would return the same results.

Example – Greater Than Operator

You can use the > operator in Oracle to test for an expression higher than.

SELECT *
FROM suppliers
WHERE supplier_id > 1000;

In this example, the SELECT announcement would return all rows from the suppliers table the place the supplier_id is increased than 1000 A supplier_id equal to one thousand would not be protected in the result set.

Example – Greater Than or Equal Operator

In Oracle, you can use the >= operator to test for an expression higher than or equal to.

SELECT *
FROM suppliers
WHERE supplier_id >= 1000;

In this example, the SELECT statement would return all rows from the suppliers desk the place the supplier_id is higher than or equal to 1000 In this case, supplier_id equal to 1000 would be covered in the end result set.

Example – Less Than Operator

You can use the < operator in Oracle to test for an expression much less than.

SELECT *
FROM employees
WHERE employee_id < 99;

In this example, the SELECT declaration would return all rows from the personnel table where the employee_id is less than 99 An employee_id equal to ninety nine would not be blanketed in the end result set.

Example – Less Than or Equal Operator

In Oracle, you can use the <= operator to test for an expression less than or equal to.

SELECT *
FROM employees
WHERE employee_id <= 99;

In this example, the SELECT declaration would return all rows from the employees table the place the employee_id is much less than or equal to ninety nine In this case, n employee_id equal to ninety nine would be blanketed in the end result set.

Example – Advanced Operators

For the greater superior contrast operators, we’ve got written unique tutorials to talk about each one individually. These matters will be protected later, or you can soar to one of these tutorials now.

IN ( ) NOT BETWEEN IS NULL IS NOT NULL LIKE REGEXP_LIKE EXISTS