Operators in SQLite

SQLite Operators

The reserved words or characters used in SQLite statements to perform operations on a variable, an expression or a value are called the SQLite Operators. It is used to specify conditions. It also acts as a conjunction for multiple conditions in a statement. SQLite facilitates four types of operators. These are:

 

Arithmetic Operators:

The various SQLite Arithmetic operators are listed below.

OPERATOR DESCRIPTION USES EXAMPLE
+ Addition Operator Adds the values present on both sides of the operator. If a = 20 and b = 30

a+b = 50

Subtraction Operator Subtracts the right hand operand from left hand operand. If a = 20 and b = 30

a-b = -10

* Multiplication Operator Multiplies the values of both sides. If a = 20 and b = 30

a*b = 600

/ Division Operator Divides the left hand operand by right hand operand. If a = 30 and b = 10

a/b = 3

% Modulus Operator Divides the left hand operand by right hand operand and returns the remainder. If a = 30 and b = 10

a%b = 0

 

Comparison Operators:

The various SQLite Comparison operators are listed below.

OPERATOR DESCRIPTION USES EXAMPLE
== Equal to Checks if the values of two operands are equal or not. If a = 30 and b = 10

(a == b) is not true.

= Equal to Checks if the values of two operands are equal or not. If a = 30 and b = 10

(a = b) is not true.

!= Not Equal to Checks if the values of two operands are equal or not. If a = 30 and b = 10

(a != b) is true.

<> Not Equal to Checks if the values of two operands are equal or not. If a = 30 and b = 10

(a <> b) is true.

> Greater than Checks if the value of left operand is greater than the value of the right operand. If a = 30 and b = 10

(a > b) is true.

< Less than Checks if the value of left operand is less than the value of the right operand. If a = 30 and b = 10

(a < b) is not true.

>= Greater than or Equal to Checks if the value of left operand is greater than or equal to the value of the right operand. If a = 30 and b = 10

(a >= b) is true.

<= Less than or Equal to Checks if the value of left operand is less than or equal to the value of the right operand. If a = 30 and b = 10

(a <= b) is not true.

!< Not Less than Checks if the value of left operand is not less than the value of the right operand. If a = 30 and b = 10

(a !< b) is true.

!> Not Greater than Checks if the value of left operand is not greater than the value of the right operand. If a = 30 and b = 10

(a !> b) is false.

 

Logical Operators:

The various SQLite Logical operators are listed below.

OPERATOR USES
AND Combines multiple conditions in a SQL statement’s WHERE clause.
BETWEEN Searches for the values that are within the range of the minimum value to the maximum value.
EXISTS Searches for the presence of a row in a specified table that meets certain criteria.
IN Compares a value to a list of literal values that have been specified.
NOT IN Negation of IN operator.
LIKE Compares a value to similar values using wildcard operators.
GLOB Compares a value to similar values using wildcard operators, but is case sensitive.
NOT Negate operator.
OR Combines multiple conditions in a SQL statement’s WHERE clause.
IS NULL Compares a value with a null value.
IS Checks for Equal to.
IS NOT Checks for Not Equal to.
|| Adds two different strings and creates a new one.
UNIQUE Searches every row of a specified table for uniqueness.

 

Bitwise Operators:

The various SQLite Bitwise operators are listed below.

OPERATOR DESCRIPTION USES EXAMPLE
& Binary AND operator To copy a bit to the result if it exists in both operands. If a = 8 = 1000 and b = 10 = 1010

a & b = 8 = 1000

| Binary OR Operator To copy a bit if it exists in either operand. If a = 8 = 1000 and b = 10 = 1010

a | b = 8 = 1010

~ Binary Ones Complement Operator Unary operator. Has the effect of ‘flipping’ bits. If a = 8 = 1000

~a = -8 = 0111

<< Binary Left Shift Operator To move the left operands value to the left by the number of bits specified by the right operand. If a = 8 = 0000 1000 and a << 2

0010 0000

>> Binary Right Shift Operator To move the left operands value to the right by the number of bits specified by the right operand. If a = 8 = 0000 1000 and a >> 2

0000 0010

 

Please Share