Here we will learn SQLite Operators with example and how to use sqlite operators (Arithmetic operators, bitwise operators, logical operators, relational operators, and binary operators) in SQLite statements to perform multiple operations.
Generally, in SQLite operators are used with Where clause to perform particular operations like arithmetic operations, logical operations in SQLite statements.
In SQLite, we have a different type of operators available those are
In SQLite, these operators will act as a conjunction between multiple conditions in SQLite statements.
In SQLite arithmetic operators are used to perform arithmetic operations like addition, subtraction, multiplication, division, etc. In SQLite statements.
The following are the different types of arithmetic operators available in SQLite. Here for examples, we are using variables x, y and holding values like x = 30, y = 20.
Operator | Description | Example |
---|---|---|
+ | Addition - It will perform addition operation. | x + y = 50 |
- | Subtraction – It will perform a subtraction operation | x - y = 10 |
* | Multiplication – It will perform a multiplication operation | x * y = 600 |
/ | Division – It will perform division operation. | x / y = 1.5 |
% | Modulus – It will perform modulus operation and return remainder value | x % y = 10 |
Generally, in SQLite arithmetic operators (addition, subtraction, division, etc.) are like a mathematical function that takes two operands and performs a calculation on them.
The following are examples of using SQLite Arithmetic operators in select statements.
sqlite> SELECT 5 + 2;
5+2
----------
7
sqlite> SELECT 5 * 2;
5*2
----------
10
In SQLite relational or comparison operators (e.g., >, <, =) are used to compare values and value expressions and return a logical value, which is either true or false.
The following are the different types of comparison or relational operators available in SQLite. Here for examples, we are using variables x, y, and holding values like x = 20, y = 30.
Operator | Description | Example |
---|---|---|
< | Less than - It will check if the left operand value less than right-hand operand value or not. | (x<y) = true |
<= | Less than equal to - It will check if the left operand value less than or equal to right-hand operand value or not. | (x<=y) = false |
> | Greater than - It will check if the left operand value greater than right-hand operand value or not. | (x>y) = true |
>= | Greater than equal to - It will check if the left operand value greater than or equal to right-hand operand value or not. | (x>=y) = true |
= | Equal to - It will check if both operand values equal or not | (x=y) = false |
== | Equal to - It will check if both operand values equal or not | (x==y) = false |
<> | Not Equal to – It will check if two operand values equal or not. In case if the values not equal then it will return true | (x<>y) = true |
!= | Not Equal to - It will check if two operand values equal or not. In case if the values not equal then it will return true | (x!=y) = true |
The following are examples of using SQLite comparison operators to perform comparison operations.
sqlite> SELECT 1 > 2;
1 > 2
----------
0
sqlite> SELECT 1 < 2;
1 < 2
----------
1
If condition satisfies, then it will return true (1) otherwise false (0).
In SQLite Logical operators (AND, OR, NOT, IN, Between, Exists, etc.) are used to define multiple conditions in SQLite statements to return rows or records from a statement based on the defined conditions.
The following are the different types of logical operators available in SQLite.
Operator | Description |
---|---|
AND | We can define multiple conditions in SQLite statements and it will return records only when all the defined conditions true. |
OR | We can define multiple conditions in SQLite statements and it will return records if any one of defined condition true. |
IN | By using IN operator we can check given value exists in the list of values or in the subquery result set. |
NOT IN | It’s just opposite to IN operator. By using NOT IN operator we can check given value exists in the list of values or in the subquery result set. |
BETWEEN | We can return values which exists between a defined range of values. |
EXISTS | By using EXISTS we can check whether the required value exists in the result set or not |
LIKE | By using LIKE operator we can get records whose values matching with a given value. |
GLOB | It's same as the LIKE operator only difference is its case sensitive |
In SQLite AND and OR operators use multiple conditions to return a result. Following are the all possible outcomes for Logical AND and OR operators.
Following table shows all the possible conditions for Logical AND operator.
Operand1 | Operand2 | Result |
---|---|---|
True | True | True |
True | False | False |
False | False | False |
The following table shows all the possible conditions for the Logical OR operator.
Operand1 | Operand2 | Result |
---|---|---|
True | True | True |
True | False | True |
False | False | False |
We will see how to use SQLite Logical operators with examples before that first create an emp_master table and insert some data to perform operations by using following queries.
CREATE TABLE emp_master
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT,
salary NUMERIC,
dept_id INTEGER);
INSERT INTO emp_master
values (1,'Honey','Patel',10100,1),
(2,'Shweta','Jariwala', 19300,2),
(3,'Vinay','Jariwala', 35100,3),
(4,'Jagruti','Viras', 9500,2),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 50000,3),
(9,'Shwets','Jariwala', 19400,2);
Now run the following query to check records of emp_master table.
sqlite> SELECT * FROM emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
1 Honey Patel 10100 1
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
9 Shwets Jariwala 19400 2
Now write SQLite query like as shown below to use logical operators in Select statement.
SELECT *
FROM emp_master
WHERE first_name = 'sunny' AND last_name = 'patel';
Now here first_name= 'sunny' and last_name= 'patel' is a two operands for AND operator.
In emp_master table we didn't have any employee with first_name = 'sunny' so first condition fails and there is one employee whose last_name = 'patel' so it becomes true. So now from the truth table of AND operator if one operand is false and another operand is true then it is evaluated as false. So we won't get any records.
This is how we can use SQLite logical operators in SQLite statements to get required data based on our requirements.
In SQLite string operator (||) is used to concatenate strings. Following is the example of using a string operator (||) in the SQLite Select statement.
sqlite> SELECT 'SQL' || 'lite';
'SQL' || 'lite'
---------------
SQLlite
This is how we can use SQLite String operators in SQLite statements to concatenate values.
In SQLite bitwise operator works on bits and performs a bit-by-bit operation. The following are the different types of bitwise operators available in SQLite.
Operator | Description | Example |
---|---|---|
& | Binary AND operator copies the bit if it exists in both operands. | 85 & 40 = 0 |
| | Binary OR operator copies the bit if it exists in either one operand. | 85 | 40 = 125 |
<< | Binary left shift operator will move left-hand operand to left by the number of bits defined in the right side operand. | 80 << 2 = 320 |
>> | Binary right shift operator will move left-hand operand to right by the number of bits defined in right side operand. | 80 >> 2 = 20 |
Following is the truth table for bitwise & and | operators as follows.
A | B | A & B | A | B |
---|---|---|---|
0 | 0 | 0 | 0 |
0 | 1 | 0 | 1 |
1 | 1 | 1 | 1 |
1 | 0 | 0 | 1 |
Now we will see how to use SQLite Bitwise operation on 85 and 40
85= 1010101
40=101000
Following is the example of using SQLite Bitwise OR Operator (|) to copies a bit to the result if it exists in either operand.
sqlite> SELECT 85 | 40;
85|40
----------
125
In the above SQLite bitwise OR operator example 125 in binary equal to 1111101.
Following is the example of using SQLite Bitwise AND operator (&) to copies a bit to the result if it exists in both operands.
sqlite> SELECT 85 & 40;
85&40
----------
0
Following is the example of using the SQLite Bitwise Left Shift Operator (<<). The SQLite left operands value is moved left by the number of bits specified by the right operand.
sqlite> SELECT 80 << 2;
80 << 2
----------
320
Here 320 = 101000000
Following is the example of using the SQLite Bitwise Right Shift Operator (>>). The SQLite right operands value is moved right by the number of bits specified by the right operand.
sqlite> SELECT 80 >> 2;
80 >> 2
----------
20
Here 20 = 10100
This is how we can use SQLite binary operators in SQLite statements to get the desired result sets based on our requirements.