We're all gonna talk about this post all of the comparison operators used in Oracle to test for equality and inequality, as well as the more advanced operators.
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 M_CORRSQL.ORDERDETAILS O
WHERE O.QUANTITY = 1000;
In this example, the SELECT statement above would return all rows from the ORDERDETAILS table where the QUANTITY is equal to 1000.
Example - Inequality Operator (Not Equal)
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 M_CORRSQL.ORDERDETAILS O
WHERE O.QUANTITY <> 1000;
In this example, the SELECT statement would return all rows from the ORDERDETAILS table where the QUANTITY is not equal to 1000.
Or you could also write this query using the != operator, as follows:
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.QUANTITY != 1000;
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.
For example,
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.QUANTITY > 1000;
In this example, the SELECT statement would return all rows from the ORDERDETAILS table where the QUANTITY is greater than 1000. A QUANTITY 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.
For example,
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.QUANTITY >= 1000;
In this example, the SELECT statement would return all rows from the ORDERDETAILS table where the QUANTITY is greater than or equal to 1000. In this case, QUANTITY 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.
For example,
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.QUANTITY < 1000;
In this example, the SELECT statement would return all rows from the ORDERDETAILS table where the QUANTITY is less than 1000. A QUANTITY equal to 1000 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.
For example,
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.QUANTITY <= 1000;
In this example, the SELECT statement would return all rows from the ORDERDETAILS table where the QUANTITY is less than or equal to 1000. In this case, n QUANTITY equal to 1000 would be included in the result set.
Example – IN Condition
how to use the Oracle IN condition with syntax and examples. The PLSQL IN condition is used to help reduce the need to use multiple OR conditions.
let's look at an Oracle IN condition example using numeric values.
For example:
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.ORDERDETAILID IN (2,4);
This Oracle IN condition example would return all ORDERDETAILS where the ORDERDETAILID is either 2 or 4.
The above IN example is equivalent to the following SELECT statement:
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE (O.ORDERDETAILID = 2 OR O.ORDERDETAILID = 4);
Both of these queries would return the same results.
Example – NOT Condition
The PLSQL NOT condition (also called the NOT Operator) is used to negate a condition.
The Oracle NOT condition can be combined with the IN condition.
For example:
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.ORDERDETAILID NOT IN (2,4);
This Oracle NOT example would return all rows from the ORDERDETAILS table where the ORDERDETAILID is not 2 or 4, it is more efficient to list the values that you do not want, as opposed to the values that you do want.
Example - Combine with BETWEEN condition
For example:
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.ORDERDETAILID NOT BETWEEN 2 AND 4;
This Oracle NOT example would return all rows where the ORDERDETAILID was NOT between 2 and 4, inclusive. It would be equivalent to the following Oracle SELECT statement:
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE (O.ORDERDETAILID < 2 OR O.ORDERDETAILID > 4);
Example - IS NULL Condition
NULL condition is used to test for a NULL value. You can use the Oracle IS NULL condition in either a SQL statement or in a block of PLSQL code.
Example - With SELECT Statement
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.QUANTITY IS NULL;
Example - With INSERT Statement
INSERT INTO M_CORRSQL.ORDERDETAILS
VALUES (7,10555,61, NULL)
Example - With UPDATE Statement
UPDATE M_CORRSQL.ORDERDETAILS
SET QUANTITY = 1000
WHERE PRODUCTID = NULL;
Example - With DELETE Statement
DELETE FROM M_CORRSQL.ORDERDETAILS WHERE PRODUCTID = NULL
Or
DELETE FROM M_CORRSQL.ORDERDETAILS WHERE PRODUCTID IS NULL
Example – IS NOT NULL Condition
The Oracle IS NOT NULL condition is used to test for a NOT NULL value. You can use the Oracle IS NOT NULL condition in either a SQL statement or in a block of PLSQL code.
Example - With SELECT Statement
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.QUANTITY IS NOT NULL;
Example - With UPDATE Statement
UPDATE M_CORRSQL.ORDERDETAILS
SET QUANTITY = 1000
WHERE PRODUCTID IS NOT NULL;
Example - With DELETE Statement
DELETE FROM M_CORRSQL.ORDERDETAILS WHERE PRODUCTID IS NOT NULL
Example – LIKE Condition
Example - With LIKE Statement
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.PRODUCTID LIKE '11';
Example – With NOT LIKE Statement
SELECT *
FROM M_CORRSQL.ORDERDETAILS O
WHERE O.PRODUCTID NOT LIKE '11';
Example – REGEXP_LIKE Condition
The Oracle REGEXP_LIKE condition allows you to perform regular expression matching to the WHERE clause. We can match on more than one alternative.
For example:
SELECT O.CUSTOMERNAME
FROM M_CORRSQL.O_CUSTOMER O
WHERE REGEXP_LIKE (CUSTOMERNAME, 'V|P');
This REGEXP_LIKE example will return all contacts whose CUSTOMERNAME is either VKasun, VNipuna, or Vimanth. The | pattern tells us to look for the letter "V" and "P".
Example - Match on beginning
SELECT O.CUSTOMERNAME
FROM M_CORRSQL.O_CUSTOMER O
WHERE REGEXP_LIKE (CUSTOMERNAME, '^V(*)');
Example - Match on end
SELECT O.CUSTOMERNAME
FROM M_CORRSQL.O_CUSTOMER O
WHERE REGEXP_LIKE (CUSTOMERNAME, '(*)a$');
0 coment�rios: