| |
 |
|
SQL Operators
Oracle Tips by
Burleson
|
SQL incorporates operators that are quite similar to the operators
in other languages, as shown in Table 2.2.
Table 2.2 SQL operators.
| Operator |
Usage |
| ** |
The exponentiation operator (2**2 IS 4) |
| NOT |
Negates a condition (IS NOT NULL, NOT IN, NOT
BETWEEN) |
| + |
The addition operator (2 + 2 IS 4) as well as the unary
indicator of a positive number (+2) |
| - |
The subtraction operator (4 - 2 IS 2) as well as the
unary indicator of a negative number (-2) |
| * |
The multiplication operator (2 * 2 IS 4) |
| / |
The division operator (4 / 2 IS 2) |
| || |
The concatenation operator (‘A’ || ‘B’ IS ‘AB’) |
| = |
The equation operator (2 = 2) |
| != |
The non-equation operator (3 != 2) |
| < |
The less than operator (2 < 4) |
| > |
The greater than operator (4 > 2) |
| <= |
The less than or equal to operator (2 <= 4, 2 <= 2)
|
| >= |
The greater than or equal to operator (4 >= 2, 2 >= 2)
|
| IS NULL |
Tests a variable or condition for a NULL value (first_name
IS NULL) |
| LIKE |
Allows wildcard searches (last_name LIKE ‘SM%’) |
| BETWEEN |
An inclusive range test (1, 2, AND 3 ARE BETWEEN 1 AND 3)
|
| IN |
A set operator (2 IS IN {1, 2, 3}) |
| AND |
A logical and (x < y AND y < z) |
| OR |
A logical or (x < y OR y < z) |
Any of these operators may be used anywhere in a DML statement with
the exception of the FROM clause, as shown by these examples:
SELECT 2**nRealValue
FROM CHECK_VALUES
WHERE nRealValueProcessed = 'F';
DELETE
FROM STUDENTS
WHERE overall_gpa < 0.2;
UPDATE STUDENTS
SET overall_gpa = 1.02 * most_recent_gpa
WHERE ssn = 999999999;
INSERT
INTO CHECK_VALUES
(nRealValueProcessed,
nRealValue)
VALUES ('F',
(2.031 ** 3) / 9);
Oracle Subqueries
A subquery is a query within the WHERE clause of a DML
statement, as shown in this example:
SELECT ssn
FROM STUDENTS
WHERE overall_gpa = (SELECT max (overall_gpa)
FROM STUDENTS);
Subqueries can be used in any type of DML statement.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
