Oracle / PLSQL: Comparison Operators
This Oracle tutorial explores all of the comparison operators used in Oracle to test for equality and inequality, as well as the more advanced operators.
Description
Comparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison 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 value in a list |
| NOT | Negates a condition |
| BETWEEN | Within a range (inclusive) |
| IS NULL | NULL value |
| IS NOT NULL | Non-NULL value |
| LIKE | Pattern matching with % and _ |
| REGEXP_LIKE | Pattern matching with regular expressions |
| EXISTS | Condition is met if subquery returns at least one row |
Some of these operators are fairly straight forward and others are more complicated. Let's begin by reviewing the easier comparison operators in Oracle.
Example - Equality Operator
In Oracle/PLSQL, you can use the = operator to test for equality in a query.
For example:
SELECT * FROM customers WHERE last_name = 'Anderson';
In this example, the SELECT statement above would return all rows from the customers table where the last_name is equal to Anderson.
Example - Inequality Operator
In Oracle/PLSQL, you can use the <> or != operators to test for inequality in a query.
For example, we could test for inequality using the <> operator, as follows:
SELECT * FROM customers WHERE last_name <> 'Anderson';
In this example, the SELECT statement would return all rows from the customers table where the last_name is not equal to Anderson.
Or you could also write this query 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 greater than.
SELECT * FROM suppliers WHERE supplier_id > 1000;
In this example, the SELECT statement would return all rows from the suppliers table where the supplier_id is greater than 1000. A supplier_id equal to 1000 would not be included in the result set.
Example - Greater Than or Equal Operator
In Oracle, you can use the >= operator to test for an expression greater than or equal to.
SELECT * FROM suppliers WHERE supplier_id >= 1000;
In this example, the SELECT statement would return all rows from the suppliers table where the supplier_id is greater than or equal to 1000. In this case, supplier_id equal to 1000 would be included in the result set.
Example - Less Than Operator
You can use the < operator in Oracle to test for an expression less than.
SELECT * FROM employees WHERE employee_id < 99;
In this example, the SELECT statement would return all rows from the employees table where the employee_id is less than 99. An employee_id equal to 99 would not be included in the 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 statement would return all rows from the employees table where the employee_id is less than or equal to 99. In this case, n employee_id equal to 99 would be included in the result set.
Example - Advanced Operators
For the more advanced comparison operators, we've written specific tutorials to discuss each one individually. These topics will be covered later, or you can jump to one of these tutorials now.
No comments:
Post a Comment