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
|
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 |