SQLite Operators

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.

SQLite Operators

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

 

  • Arithmetic Operators
  • Comparison (Relational) Operators
  • Logical Operators
  • Bitwise Operators

In SQLite, these operators will act as a conjunction between multiple conditions in SQLite statements.

SQLite Arithmetic Operators

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.

 

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

SQLite Arithmetic Operators Example

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.

 

sqliteSELECT2;

 

5+2

----------

7

 

sqliteSELECT2;

 

5*2

----------

10

SQLite Comparison (Relational) Operators 

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.

 

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

SQLite Comparison Operators Example

The following are examples of using SQLite comparison operators to perform comparison operations.

 

sqliteSELECT 1 > 2;

 

1 > 2

----------

0

 

sqliteSELECT 1 < 2;

 

1 < 2

----------

1

If condition satisfies, then it will return true (1) otherwise false (0).

SQLite Logical Operators

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.

 

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

 

Operand1Operand2Result
True True True
True False False
False False False

The following table shows all the possible conditions for the Logical OR operator.

 

Operand1Operand2Result
True True True
True False True
False False False

SQLite Logical Operators Example

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.

SQLite String operator

In SQLite string operator (||) is used to concatenate strings. Following is the example of using a string operator (||) in the SQLite Select statement.

 

sqliteSELECT 'SQL' || 'lite';

 

'SQL' || 'lite'

---------------

SQLlite

This is how we can use SQLite String operators in SQLite statements to concatenate values.

SQLite Bitwise operator

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.

 

OperatorDescriptionExample
& 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.

 

ABA & BA | B
0 0 0 0
0 1 0 1
1 1 1 1
1 0 0 1

SQLite Bitwise Operators Examples

Now we will see how to use SQLite Bitwise operation on 85 and 40

 

85= 1010101

40=101000

SQLite Bitwise OR (|) Operator

Following is the example of using SQLite Bitwise OR Operator (|) to copies a bit to the result if it exists in either operand.

 

sqliteSELECT 85 40;

 

85|40

----------

125

In the above SQLite bitwise OR operator example 125 in binary equal to 1111101.

SQLite Bitwise AND (&) Operator

Following is the example of using SQLite Bitwise AND operator (&) to copies a bit to the result if it exists in both operands.

 

sqliteSELECT 85 40;

 

85&40

----------

0

SQLite Bitwise Left Shift (<<) Operator

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.

 

sqliteSELECT 80 << 2;

 

80 << 2

----------

320

Here 320 = 101000000

SQLite Bitwise Right Shift (>>) Operator

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.

 

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