SQL Operators and Conditions

What is Operators and Conditions ?
Operators and conditions are used to perform operations, such as addition or subtraction, or comparisons on the data items in SQL statement. Operators are represented by single characters or reserved words.

A condition is an expression of several operators or expressions that evaluated to True, False, or Unknown.

There are two types of operators. Binary and Unary. The unary operator operators on only one operand. For example, to indicate a negative 10, use the unary operator to write 10. The binary operator operators on two operands. For example, to subtract two numbers you would write 6 4.

Why use Operators and Conditions?

Operators and conditions are necessary features of any computer language. They enable you to perform arithmetic, data comparisons, and a variety of other data manipulations that are necessary to support your application requirements.

These Oracle tools can assist you in selecting specific set of rows. For example, you could request a set of rows where deptno 15. Then you would receive only the rows where the deptno is equal to 15. In this example, the operator was used.

How to use Operators?

Operators are used to manipulate individual data items and return a result. The following sections deal with different types of operators: arithmetic, character, comparison, logical, set and other types.

Arithmetic Operators

Arithmetic operators are used in SQL expressions to add, subtract, multiply, divide, and negate data values. The result of this expression is a numeric value. Table below shows the arithmetic operators.

The Arithmetic Operators

+,- - Denotes a positive or negative expression. These are unary operators.
* - Multiplies . This a binary operator.
/ - Divides. This is a binary operator.
+ - Adds. This is a binary operator.
- - Subtracts. This is a binary operator.

Comparison Operators

Comparison operators are used to compare one expression to another expression. The result of the comparison is True, False, or Unknown. Table below lists the comparison operators with their descriptions.

The comparison operators

=Equality comparison
!= < >,V=Inequality comparison
>Greater than
<Less than
<=Greater than or equal to
>=Less than or equal to
INEqual to any member of
NOT INNot equal to any member of
IS NULLTests for null
IS NOT NULLTests for anything other than nulls
LIKE - Returns true when the first expression matches the pattern of the second
ALL - Compares a value to every value in a list
ANY, SOME - Compares a value to each value in a list
EXISTS - True if a subquery returns at least one row
BETWEEN X and Y - Greater than or equal to X and less than or equal to Y

Logical operators

A logical operator is used to produce a single result from the combing of two separate conditions. The logical operators are shown below
AND - Returns True if both component conditions are true; otherwise False.
OR - Returns True if either component condition is true or unknown otherwise return False.
NOT - Returns True if the following condition is false; otherwise returns false

The Set Operators

Set operators combine the results of two separate queries into a single result. The set operators are described below.

UNION Returns all distinct rows from both queries
UNION ALL Returns all rows from both queries, including duplicate rows
INTERSECT Returns all rows that are selected by both queries
MINUS Returns all distinct rows that are in the first query but not in the second query.

Domain Name Search