SqliteoperatorWhat is the SQLite operator?
An operator is a reserved word or character that is primarily used in the WHERE clause of an SQLite statement, such as comparison and arithmetic operations.
Operator is used to specify conditions in the SQLite statement and to concatenate multiple conditions in the statement.
-
Arithmetic operators
-
Comparison operators
-
logical operators
-
Bitwise operators
SQLite arithmetic operators
Assuming the variable a=10, the variable b=20, then:
operator |
Description |
Example |
+ |
Add-Adds the values on both sides of the operator |
A + B will get 30 |
- |
Subtraction-left operand minus right operand |
A-B will get-10 |
* |
Multiplication-multiplies the values on both sides of the operator |
A * B will get 200 |
/ |
Division-left operand divided by right operand |
B/A will get 2 |
% |
Modulo-the remainder of the left operand divided by the right operand |
B% A would give 0 |
Instance
Here is a simple example of the SQLite arithmetic operator:
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
Assuming the variable a=10, the variable b=20, then:
operator |
Description |
Example |
== |
Check that the values of the two operands are equal, and if they are equal, the condition is true. |
(A = = B) is not true. |
= |
Check that the values of the two operands are equal, and if they are equal, the condition is true. |
(A = b) is not true. |
!= |
Check that the values of the two operands are equal, and the condition is true if they are not equal. |
(A! = B) is true. |
<> |
Check that the values of the two operands are equal, and the condition is true if they are not equal. |
(a <> B) is true. |
> |
Checks if the value of the left operand is greater than the value of the right operand, and if so, the condition is true. |
(A > B) is not true. |
< |
Checks if the value of the left operand is less than the value of the right operand, or if the condition is true. |
(A < b) is true. |
>= |
Checks if the value of the left operand is greater than or equal to the right operand, and if so the condition is true. |
(a >= B) is not true. |
<= |
Checks if the value of the left operand is less than or equal to the right operand, and if so the condition is true. |
(a <= B) is true. |
!< |
Checks whether the value of the left operand is not less than the value of the right operand, or if the condition is true. |
(a!< B) is false. |
!> |
Checks if the value of the left operand is not greater than the value of the right operand, and if so, the condition is true. |
(a!> B) is true. |
Instance
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 example shows the use of various SQLite comparison operators.
here, we use the
where clause, which will be explained in a separate section behind, but now you need to understand that the WHERE clause is the conditional statement used to set the SELECT statement.
The following SELECT statement lists all records for SALARY greater than 50,000.00:
sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000;
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 that SALARY equals 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 for which 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 for which 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 with SALARY 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
The following is a list of all the logical operators in SQLite.
operator |
Description |
and |
The AND operator allows the existence of multiple conditions in the WHERE clause of an SQL statement. |
Between |
The between operator is used to search for a value within a range of values for a given minimum and maximum value. |
EXISTS |
The EXISTS operator is used to search for the existence of a row in a specified table that satisfies certain criteria. |
Inch |
The in operator is used to compare a value to a series of values in a specified list. |
Not in |
The opposite of the in operator, which is used to compare a value to a value that is not in a series of specified lists. |
Like |
The LIKE operator is used to compare a value to a similar value using a wildcard operator. |
GLOB |
The GLOB operator is used to compare a value to a similar value using a wildcard operator. The difference between GLOB and like is that it is case-sensitive. |
Not |
The NOT operator is the opposite of the logical operator used. such as not EXISTS, not between, not in, and so on. It is a negation operator. |
OR |
The OR operator is used to combine multiple conditions in a WHERE clause of an SQL statement. |
Is NULL |
The null operator is used to compare a value to a null value. |
Is |
The IS operator is similar to =. |
is not |
The is not operator is similar to! =. |
|| |
Connect two different strings and get a new string. |
UNIQUE |
The unique operator searches for each row in the specified table, ensuring uniqueness (No duplicates). |
Instance
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 example illustrates the use of the SQLite logical operators.
The following SELECT statement lists all records with age greater than or equal to 65000.00 and wages greater than or equal to:
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 with age greater than or equal to or wages 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 for which age is not null, and the result shows all records, meaning that there is no record of 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 that NAME begins with ' Ki ' and does not limit the characters after ' 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 that NAME begins with ' Ki ' and does not limit the characters after ' 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 of age with a value of 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 the value of 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 for the age value 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
The following SELECT statement uses the SQL subquery, which finds all records with the age field in SALARY > 65000, and the next where clause is used with the EXISTS operator to list all of the entries in the results returned by the subquery in the outer query. Recorded:
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 the SQL subquery, which finds all records with the age field in SALARY > 65000, and the next where clause is used with the > operator, listing all of the ages in the outer query that are older than the results returned by the subquery. Recorded:
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 operator
Bitwise operators Act on BITS and perform bitwise operations. Truth Table & and | 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 that if A = 60, and B = 13, now in binary format, they are as follows:
A = 0011 1100
B = 0000 1101
-----------------
a&b = 0000 1100
a| B = 0011 1101
~a = 1100 0011
The bitwise operators supported by the SQLite language are listed in the following table. Assuming the variable a=60, the variable b=13, then:
operator |
Description |
Example |
& |
If both are present in two operands, the binary and operator copies one to the result. |
(A & B) will be 12, i.e. 0000 1100 |
| |
If it exists in either operand, the binary OR operator copies one into the result. |
(A | B) will get 61, i.e. 0011 1101 |
~ |
The binary complement operator is a unary operator with a "flip" bit effect. |
(~a) will get-61, which is 1100 0011,2 of the complement form, signed binary number. |
<< |
Binary left shift operator. The value of the left operand moves the number of digits specified by the right operand to the left. |
A << 2 will get 240, which is 1111 0000 |
>> |
Binary right-shift operator. The value of the left operand moves the right operand to the specified number of digits. |
A >> 2 will get 15, which is 0000 1111 |
Instance
The following example demonstrates the use of the SQLite bitwise operator:
sqlite> .mode line
sqlite> select 60 | 13;
60 | 13 = 61
sqlite> select 60 & 13;
60 & 13 = 12
sqlite> select 60 ^ 13;
10 * 20 = 200
sqlite> select (~60);
(~60) = -61
sqlite> select (60 << 2);
(60 << 2) = 240
sqlite> select (60 >> 2);
(60 >> 2) = 15
SQLite using the tutorial 10 operator