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.

OPERATORDESCRIPTIONUSESEXAMPLE
+Addition OperatorAdds the values present on both sides of the operator.If a = 20 and b = 30

a+b = 50

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

a-b = -10

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

a*b = 600

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

a/b = 3

%Modulus OperatorDivides 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.

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

(a == b) is not true.

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

(a = b) is not true.

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

(a != b) is true.

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

(a <> b) is true.

>Greater thanChecks 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 thanChecks 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 toChecks 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 toChecks 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 thanChecks 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 thanChecks 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.

OPERATORUSES
ANDCombines multiple conditions in a SQL statement’s WHERE clause.
BETWEENSearches for the values that are within the range of the minimum value to the maximum value.
EXISTSSearches for the presence of a row in a specified table that meets certain criteria.
INCompares a value to a list of literal values that have been specified.
NOT INNegation of IN operator.
LIKECompares a value to similar values using wildcard operators.
GLOBCompares a value to similar values using wildcard operators, but is case sensitive.
NOTNegate operator.
ORCombines multiple conditions in a SQL statement’s WHERE clause.
IS NULLCompares a value with a null value.
ISChecks for Equal to.
IS NOTChecks for Not Equal to.
||Adds two different strings and creates a new one.
UNIQUESearches every row of a specified table for uniqueness.

 

Bitwise Operators:

The various SQLite Bitwise operators are listed below.

OPERATORDESCRIPTIONUSESEXAMPLE
&Binary AND operatorTo 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 OperatorTo copy a bit if it exists in either operand.If a = 8 = 1000 and b = 10 = 1010

a | b = 8 = 1010

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

~a = -8 = 0111

<<Binary Left Shift OperatorTo 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 OperatorTo 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