What is an Operator in SQLite?

An operator is a reserved word or a character used primarily in an SQLite statement”s WHERE clause to perform operation(s), such as comparisons and arithmetic operations.

Operators are used to specify conditions in an SQLite statement and to serve as conjunctions for multiple conditions in a statement.

• Arithmetic operators
• Comparison operators
• Logical operators
• Bitwise operators

SQLite Arithmetic Operators

Assume variable a holds 10 and variable b holds 20, then SQLite arithmetic operators will be used as follows −

SQLite Comparison Operators

Assume variable a holds 10 and variable b holds 20, then SQLite comparison operators will be used as follows

SQLite Logical Operators

Here is a list of all the logical operators available in SQLite.

SQLite Bitwise Operators

Bitwise operator works on bits and performs bit-by-bit operation. Following is the truth table for & and |.

Assume if A = 60; and B = 13, then 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 Bitwise operators supported by SQLite language are listed in the following table. Assume variable A holds 60 and variable B holds 13, then −

SQLite WHERE clause is used to specify a condition while fetching the data from one table or multiple tables.

If the given condition is satisfied, means true, then it returns the specific value from the table. You will have to use WHERE clause to filter the records and fetching only necessary records.

The WHERE clause not only is used in SELECT statement, but it is also used in UPDATE, DELETE statement, etc., which will be covered in subsequent chapters.

Syntax

Following is the basic syntax of SQLite SELECT statement with WHERE clause.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition]

Example

You can specify a condition using such as >, <, =, LIKE, NOT, etc. Consider COMPANY table with 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

Following is a simple examples showing the usage of SQLite Logical Operators. Following SELECT statement lists down all the 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

Following SELECT statement lists down all the 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

Following SELECT statement lists down all the records where AGE is not NULL, which means all the records because none of the 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

Following SELECT statement lists down all the records where NAME starts with ”Ki”, does not matter what comes after ”Ki”.

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE ''Ki%

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

Following SELECT statement lists down all the records where NAME starts with ”Ki”, does not matter what comes after ”Ki”.

sqlite> SELECT * FROM COMPANY WHERE NAME GLOB ''Ki*

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0

Following SELECT statement lists down all the records where AGE value 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

Following SELECT statement lists down all the records where AGE value 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

Following SELECT statement lists down all the records where AGE value is in 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

Following SELECT statement makes use of SQL sub-query, where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with EXISTS operator to list down all the records where AGE from the outside query exists in the result returned by the sub-query −

sqlite> SELECT AGE FROM COMPANY
   WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

AGE
----------
32
25
23
25
27
22
24

Following SELECT statement makes use of SQL sub-query where sub-query finds all the records with AGE field having SALARY > 65000 and later WHERE clause is being used along with > operator to list down all the records where AGE from the outside query is greater than the age in the result returned by the sub-query.

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 DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete the selected rows, otherwise all the records would be deleted.

Syntax

Following is the basic syntax of DELETE query with WHERE clause.

DELETE FROM table_name
WHERE [condition];

You can combine N number of conditions using AND or OR operators.

Example

Consider COMPANY table with 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

Following is an example, which will DELETE a customer whose ID is 7.

sqlite> DELETE FROM COMPANY WHERE ID = 7;

Now COMPANY table will have 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

If you want to DELETE all the records from COMPANY table, you do not need to use WHERE clause with DELETE query, which will be as follows −

sqlite> DELETE FROM COMPANY;

Now, COMPANY table does not have any record as all the records have been deleted by DELETE statement.

SQLite AND & OR operators are used to compile multiple conditions to narrow down the selected data in an SQLite statement. These two operators are called conjunctive operators.

These operators provide a means to make multiple comparisons with different operators in the same SQLite statement.

The AND Operator

The AND operator allows the existence of multiple conditions in a SQLite statement”s WHERE clause. While using AND operator, complete condition will be assumed true when all the conditions are true. For example, [condition1] AND [condition2] will be true only when both condition1 and condition2 are true.

Syntax

Following is the basic syntax of AND operator with WHERE clause.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

You can combine N number of conditions using AND operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE.

Example

Consider COMPANY table with 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

Following SELECT statement lists down all the 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 OR Operator

The OR operator is also used to combine multiple conditions in a SQLite statement”s WHERE clause. While using OR operator, complete condition will be assumed true when at least any of the conditions is true. For example, [condition1] OR [condition2] will be true if either condition1 or condition2 is true.

Syntax

Following is the basic syntax of OR operator with WHERE clause.

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

You can combine N number of conditions using OR operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR must be TRUE.

Example

Consider COMPANY table with 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

Following SELECT statement lists down all the 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

SQLite UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows, otherwise all the rows would be updated.

Syntax

Following is the basic syntax of UPDATE query with WHERE clause.

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

You can combine N number of conditions using AND or OR operators.

Example

Consider COMPANY table with 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

Following is an example, which will update ADDRESS for a customer whose ID is 6.

sqlite> UPDATE COMPANY SET ADDRESS = ''Texas'' WHERE ID = 6;

Now, COMPANY table will have 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          Texas       45000.0
7           James       24          Houston     10000.0

If you want to modify all ADDRESS and SALARY column values in COMPANY table, you do not need to use WHERE clause and UPDATE query will be as follows −

sqlite> UPDATE COMPANY SET ADDRESS = ''Texas'', SALARY = 20000.00;

Now, COMPANY table will have the following records −

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          Texas       20000.0
2           Allen       25          Texas       20000.0
3           Teddy       23          Texas       20000.0
4           Mark        25          Texas       20000.0
5           David       27          Texas       20000.0
6           Kim         22          Texas       20000.0
7           James       24          Texas       20000.0

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value.

SQL expressions are like formulas and they are written in query language. You can also use to query the database for a specific set of data.

Syntax

Consider the basic syntax of the SELECT statement as follows −

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

Following are the different types of SQLite expressions.

SQLite – Boolean Expressions

SQLite Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax −

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

Consider COMPANY table with 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

Following is a simple examples showing the usage of SQLite Boolean Expressions −

sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           James        24          Houston   10000.0

SQLite – Numeric Expression

These expressions are used to perform any mathematical operation in any query. Following is the syntax −

SELECT numerical_expression as OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

Here, numerical_expression is used for mathematical expression or any formula. Following is a simple example showing the usage of SQLite Numeric Expressions.

sqlite> SELECT (15 + 6) AS ADDITION
ADDITION = 21

There are several built-in functions such as avg(), sum(), count(), etc., to perform what is known as aggregate data calculations against a table or a specific table column.

sqlite> SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
RECORDS = 7

SQLite – Date Expressions

Date Expressions returns the current system date and time values. These expressions are used in various data manipulations.

sqlite> SELECT CURRENT_TIMESTAMP;
CURRENT_TIMESTAMP = 2013-03-17 10:43:35

SQLite GLOB operator is used to match only text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the GLOB operator will return true, which is 1. Unlike LIKE operator, GLOB is case sensitive and it follows syntax of UNIX for specifying THE following wildcards.

• The asterisk sign (*)
• The question mark (?)

The asterisk sign (*) represents zero or multiple numbers or characters. The question mark (?) represents a single number or character.

Syntax

Following is the basic syntax of * and ?.

SELECT FROM table_name
WHERE column GLOB ''XXXX*''
or
SELECT FROM table_name
WHERE column GLOB ''*XXXX*''
or
SELECT FROM table_name
WHERE column GLOB ''XXXX?''
or
SELECT FROM table_name
WHERE column GLOB ''?XXXX''
or
SELECT FROM table_name
WHERE column GLOB ''?XXXX?''
or
SELECT FROM table_name
WHERE column GLOB ''????''

You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.

Example

Following table lists a number of examples showing WHERE part having different GLOB clause with ”*” and ”?” operators.

Let us take a real example, consider COMPANY table with 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

Following is an example, which will display all the records from COMPANY table, where AGE starts with 2.

sqlite> SELECT * FROM COMPANY WHERE AGE  GLOB ''2*

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
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

Following is an example, which will display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text −

sqlite> SELECT * FROM COMPANY WHERE ADDRESS  GLOB ''*-*

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

SQLite LIKE operator is used to match text values against a pattern using wildcards. If the search expression can be matched to the pattern expression, the LIKE operator will return true, which is 1. There are two wildcards used in conjunction with the LIKE operator −

• The percent sign (%)
• The underscore (_)

The percent sign represents zero, one, or multiple numbers or characters. The underscore represents a single number or character. These symbols can be used in combinations.

Syntax

Following is the basic syntax of % and _.

SELECT FROM table_name
WHERE column LIKE ''XXXX%''
or
SELECT FROM table_name
WHERE column LIKE ''%XXXX%''
or
SELECT FROM table_name
WHERE column LIKE ''XXXX_''
or
SELECT FROM table_name
WHERE column LIKE ''_XXXX''
or
SELECT FROM table_name
WHERE column LIKE ''_XXXX_''

You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.

Example

Following table lists a number of examples showing WHERE part having different LIKE clause with ”%” and ”_” operators.

Let us take a real example, consider COMPANY table with 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

Following is an example, which will display all the records from COMPANY table where AGE starts with 2.

sqlite> SELECT * FROM COMPANY WHERE AGE LIKE ''2%

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
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

Following is an example, which will display all the records from COMPANY table where ADDRESS will have a hyphen (-) inside the text.

sqlite> SELECT * FROM COMPANY WHERE ADDRESS  LIKE ''%-%

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0

HAVING clause enables you to specify conditions that filter which group results appear in the final results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by GROUP BY clause.

Syntax

Following is the position of HAVING clause in a SELECT query.

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING clause must follow GROUP BY clause in a query and must also precede ORDER BY clause if used. Following is the syntax of the SELECT statement, including HAVING clause.

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Example

Consider COMPANY table with 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
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

Following is the example, which will display the record for which the name count is less than 2.

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000
5           David       27          Texas       85000
6           Kim         22          South-Hall  45000
4           Mark        25          Rich-Mond   65000
3           Teddy       23          Norway      20000

Following is the example, which will display the record for which the name count is greater than 2.

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
10          James       45          Texas       5000

SQLite LIMIT clause is used to limit the data amount returned by the SELECT statement.

Syntax

Following is the basic syntax of SELECT statement with LIMIT clause.

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

Following is the syntax of LIMIT clause when it is used along with OFFSET clause.

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

SQLite engine will return rows starting from the next row to the given OFFSET as shown below in the last example.

Example

Consider COMPANY table with 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

Following is an example, which limits the row in the table according to the number of rows you want to fetch from table.

sqlite> SELECT * FROM COMPANY LIMIT 6;

This will produce the following result.

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

However in certain situations, you may need to pick up a set of records from a particular offset. Here is an example, which picks up 3 records starting from the 3^rd position.

sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

This will produce the following result.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0