Monday , September 23 2019
Home / SQL / SQL Operators

SQL Operators

An Operator is a system defined keyword or a special character or a special sign. It is used to perform a specific operation on data items and finally returns a result. These data items are known as operand or arguments. The Operator can work on one or two operands. Operator can be used in SQL WHERE clause to specify conditions and also can perform arithmetic operations.

Example:

z = x + y

Here x, y, z are operands and +, = are operators.

There are two kinds of operator:

  • Unary Operator: The Unary operator works on single operand.
  • Binary Operator: The Binary operator works on two operands.

The operators have been grouped into several categories depending on operation type. Those are listed below:

1) Arithmetic Operator:

Arithmetic operators are used to perform arithmetical calculation on numeric operands. Some arithmetic operators, (+) and (-) are also used to in date time calculation.

The arithmetic operators are –

Operator Operator Name Description
+ Addition Used to add two or more numbers.
Subtraction Used to subtract from one number to another.
* Multiplication Used to multiply two or more numbers.
/ Division Used to divide one number by another.
% Module It will return remainder of a division.

2) Comparison Operator:

        Comparison Operators are used to compare one expression with another expression by conditions.

Operator Operator Name Description
= Equal Used to equality test between two operands.
Greater Than Used to compare whether one operand is greater than another operand.
>= Greater Than Equal Used to compare whether one operand is greater than or equal to another operand.
Less Than Used to compare whether one operand is less than another operand.
<= Less Than Equal Used to compare whether one operand is less than or equal to another operand.
!=, <>, ^= Not Equal Used to inequality test between two operands.
IN IN The IN operator will compare and match values in a list.
NOT IN NOT IN It will negate the IN () condition.
BETWEEN BETWEEN It will match values within a range.
NOT BETWEEN NOT BETWEEN It will negate the BETWEEN range.
LIKE LIKE It will compare and match with a pattern.
NOT LIKE NOT LIKE It will negate the LIKE operator.
IS NULL IS NULL Used to test null values.
IS NOT NULL   It will return false if the value is null. Otherwise return true.
EXISTS   It will return true if the Subquery results single or multiple rows. Otherwise false.
NOT EXISTS   It will negate the EXISTS. It will return true when a Subquery will not return any value.

3) Logical Operator:

The Logical Operators are used to combine two or more than one conditions. The logical operators are AND, OR, NOT.

Operator Description
AND The AND operator combines multiple conditions and returns TRUE if all conditions are satisfied.

Suppose you have combined five conditions with AND operator and four conditions have been successfully satisfied but one condition has been FALSE. In this situation AND operator will return FALSE. So, to be TRUE in AND operator, every condition combined by AND operator should be satisfied.

OR The OR operator combines multiple conditions and returns TRUE if at least one condition is satisfied.

Suppose you have combined five conditions with OR operator and four conditions have been FALSE but one condition has been TRUE. In this situation, OR operator will return TRUE. So, to be TRUE in OR operator, at least one condition combined by OR operator should be satisfied.

NOT The NOT operator negates a condition. If a condition is TRUE then the NOT operator will return FALSE. So, if you want to query those rows that will not satisfy the condition, in this situation you can use NOT operator before the condition.

4) Set Operator:

The SET operators are used to combine or merge the results returned by multiple queries into a single result set. The SET Operators are UNION, UNION ALL, INTERSECT, INTERSECT ALL and MINUS.

5) Character Operator:

The Character operator works on character strings. The character operator is Concatenation operator. We express Concatenation operator by the special sign ||. This concatenation operator is used to concatenate multiple character or strings.

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

One comment

  1. Merely a smiling visitant here to share the love (:, btw outstanding design .

Leave a Reply

Your email address will not be published. Required fields are marked *