SQLite Operators
What are SQLite Operators?
An operator is a reserved word or character primarily used in the WHERE clause of SQLite statements to perform operations such as comparison and arithmetic.
Operators are used to specify conditions in SQLite statements and to connect multiple conditions within a statement.
-
Arithmetic Operators
-
Comparison Operators
-
Logical Operators
-
Bitwise Operators
SQLite Arithmetic Operators
Assume variable a=10 and variable b=20, then:
| Operator | Description | Example |
|---|---|---|
| + | Addition - Adds values on either side of the operator | a + b will give 30 |
| - | Subtraction - Subtracts the right operand from the left operand | a - b will give -10 |
| * | Multiplication - Multiplies values on either side of the operator | a * b will give 200 |
| / | Division - Divides the left operand by the right operand | b / a will give 2 |
| % | Modulus - Divides the left operand by the right operand and returns the remainder | b % a will give 0 |
Example
Here is a simple example of SQLite arithmetic operators:
sqlite> .mode line
sqlite> select 10 + 20;
10 + 20 = 30
sqlite> select 10 - 20;
10 - 20 = -10
sqlite> select 10 * 20;
10 * 20 = 200
sqlite> select 10 / 5;
10 / 5 = 2
sqlite> select 12 % 5;
12 % 5 = 2
SQLite Comparison Operators
Assume variable a=10 and variable b=20, then:
| Operator | Description | Example |
|---|---|---|
| == | Checks if the values of two operands are equal, if yes then the condition becomes true. | (a == b) is not true. |
| = | Checks if the values of two operands are equal, if yes then the condition becomes true. | (a = b) is not true. |
| != | Checks if the values of two operands are equal, if not then the condition becomes true. | (a != b) is true. |
| <> | Checks if the values of two operands are equal, if not then the condition becomes true. | (a <> b) is true. |
| > | Checks if the value of the left operand is greater than the value of the right operand, if yes then the condition becomes true. | (a > b) is not true. |
| < | Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true. | (a < b) is true. |
| >= | Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes then the condition becomes true. | (a >= b) is not true. |
| <= | Checks if the value of the left operand is less than or equal to the value of the right operand, if yes then the condition becomes true. | (a <= b) is true. |
| !< | Checks if the value of the left operand is not less than the value of the right operand, if yes then the condition becomes true. | (a !< b) is false. |
| !> | Checks if the value of the left operand is not greater than the value of the right operand, if yes then the condition becomes true. | (a !> b) is true. |
Example
Assume the COMPANY table has the following records:
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
The following example demonstrates the usage of various SQLite comparison operators.
Here, we use the WHERE clause, which will be explained in a separate chapter later, but for now, you need to understand that the WHERE clause is used to set conditions for the SELECT statement.
The following SELECT statement lists all records with SALARY greater than 50,000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000;
ID NAME AGE ADDRESS SALARY
The following SELECT statement lists all records where SALARY is equal to 20,000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY = 20000; ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0
The following SELECT statement lists all records where SALARY is not equal to 20,000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY != 20000; ID NAME AGE ADDRESS SALARY
2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
The following SELECT statement lists all records where SALARY is not equal to 20,000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY <> 20000; ID NAME AGE ADDRESS SALARY
2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
The following SELECT statement lists all records where SALARY is greater than or equal to 65,000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY >= 65000; ID NAME AGE ADDRESS SALARY
4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
## SQLite Logical Operators
Below is a list of all logical operators in SQLite.
| Operator | Description |
| --- | --- |
| AND | The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause. |
| BETWEEN | The BETWEEN operator is used to search for values that are within a set of minimum and maximum values, given the min and max values inclusive. |
| EXISTS | The EXISTS operator is used to search for the presence of rows in a specified table that meet certain conditions. |
| IN | The IN operator is used to compare a value with a list of specified values. |
| NOT IN | The NOT IN operator is the opposite of the IN operator, used to compare a value with a list of values that are not specified. |
| LIKE | The LIKE operator is used to compare a value with similar values using wildcard operators. |
| GLOB | The GLOB operator is used to compare a value with similar values using wildcard operators. GLOB differs from LIKE in that it is case-sensitive. |
| NOT | The NOT operator is the opposite of all logical operators. For example, NOT EXISTS, NOT BETWEEN, NOT IN, etc. It is a negation operator. |
| OR | The OR operator is used to combine multiple conditions in the WHERE clause of an SQL statement. |
| IS NULL | The NULL operator is used to compare a value with a NULL value. |
| IS | The IS operator is similar to the = operator. |
| IS NOT | The IS NOT operator is similar to the != operator. |
| || | Concatenates two different strings to form a new string. |
| UNIQUE | The UNIQUE operator searches each row in the specified table to ensure uniqueness (no duplicates). |
## Example
Suppose the COMPANY table has the following records:
ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
The following examples demonstrate the usage of SQLite logical operators.
The following SELECT statement lists all records where AGE is greater than or equal to 25 **and** SALARY is greater than or equal to 65000.00:
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY
4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
The following SELECT statement lists all records where AGE is greater than or equal to 25 **or** SALARY is greater than or equal to 65000.00:
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY
1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
The following SELECT statement lists all records where AGE is not NULL, showing all records, which means no record has AGE equal to NULL:
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
The following SELECT statement lists all records where NAME starts with 'Ki', with no restriction on characters following 'Ki':
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0
The following SELECT statement lists all records where NAME starts with 'Ki', with no restriction on characters following 'Ki':
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0
The following SELECT statement lists all records where AGE is either 25 or 27:
sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
The following SELECT statement lists all records where AGE is neither 25 nor 27:
sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
3 Teddy 23 Norway 20000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
The following SELECT statement lists all records where AGE is between 25 and 27:
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
The following SELECT statement uses an SQL subquery. The subquery finds all records with the AGE field where SALARY > 65000, and the subsequent WHERE clause, used with the EXISTS operator, lists all records in the outer query where AGE exists in the results returned by the subquery:
sqlite> SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
AGE
----------
32
25
23
25
27
22
24
The following SELECT statement uses an SQL subquery. The subquery finds all records with the AGE field where SALARY > 65000, and the subsequent WHERE clause, used with the > operator, lists all records in the outer query where AGE is greater than the ages in the results returned by the subquery:
sqlite> SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
SQLite Bitwise Operators
Bitwise operators work on bits and perform bit-by-bit operations. The truth tables for & and | are as follows:
| p | q | p & q | p | q |
|---|---|---|---|---|
| 0 | 0 | 0 | 0 | |
| 0 | 1 | 0 | 1 | |
| 1 | 1 | 1 | 1 | |
| 1 | 0 | 0 | 1 |
Suppose if A = 60, and B = 13, in binary format, they will be as follows:
A = 0011 1100
B = 0000 1101
A&B = 0000 1100
A|B = 0011 1101
~A = 1100 0011
The following table lists the bitwise operators supported by SQLite. Assume variable A holds 60 and variable B holds 13, then:
| Operator | Description | Example | ||
|---|---|---|---|---|
| & | Binary AND Operator copies a bit to the result if it exists in both operands. | (A & B) will give 12, which is 0000 1100 | ||
| Binary OR Operator copies a bit if it exists in either operand. | (A | B) will give 61, which is 0011 1101 | ||
| ~ | Binary Ones Complement Operator is unary and has the effect of 'flipping' bits. | (~A ) will give -61, which is 1100 0011 in 2's complement form due to a signed binary number. | ||
| << | Binary Left Shift Operator. The left operands value is moved left by the number of bits specified by the right operand. | A << 2 will give 240, which is 1111 0000 | ||
| >> | Binary Right Shift Operator. The left operands value is moved right by the number of bits specified by the right operand. | A >> 2 will give 15, which is 0000 1111 |
Examples
The following examples demonstrate the usage of SQLite bitwise operators:
sqlite> .mode line
sqlite> select 60 | 13;
60 | 13 = 61
sqlite> select 60 & 13;
60 & 13 = 12
sqlite> select (~60);
(~60) = -61
sqlite> select (60 << 2);
(60 << 2) = 240
sqlite> select (60 >> 2);
(60 >> 2) = 15