SQLite Indexed By

Here we will learn what is indexed by in SQLite and how to use SQLite indexed by with examples.

SQLite Indexed By

In SQLite “INDEXED BY” clause is used to force the query planner to use a specified index while querying on the table.


Generally, in SQLite we will define INDEXED BY in query like “INDEXED BY index-name” to return values in a preceding table based on a defined index. In case if the specified index-name does not exist then the SQLite statement will throw an error.


In SQLite mostly INDEXED BY is used with SELECT, UPDATE and DELETE statements and it is not portable with any other database engines.

Syntax of SQLite Indexed By

Following is the syntax of using SQLite INDEXED BY with SELECT / UPDATE / DELETE statements.



INDEXED BY (index_name) table_name


Example of SQLite Indexed By

Now we will see how to use SQLite INDEXED BY Clause with example for that create emp_master table and insert some data using following statements.


CREATE TABLE emp_master


first_name TEXT,

last_name TEXT,

salary NUMERIC,

dept_id INTEGER);


INSERT INTO emp_master

values (2,'Shweta','Jariwala', 19300,2),

(3,'Vinay','Jariwala', 35100,3),

(4,'Jagruti','Viras', 9500,2),


(6,'sonal','Menpara', 13000,1),

(7,'Yamini','Patel', 10000,2),

(8,'Khyati','Shah', 500000,3),


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

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

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 

12          Sonal       Menpara     20000       4

Now we will create index (idx_emp_salary) on salary column of the emp_master table using the following statement.


CREATE INDEX idx_emp_salary ON emp_master(salary);

Now we will use INDEXED BY clause on the emp_master table to get data using a newly created index (idx_emp_salary) like as shown below.


SELECT * FROM emp_master INDEXED BY idx_emp_salary WHERE salary > 12000;

If you observe the above query we used “INDEXED BY” statement to fetch records from the emp_master table based on the idx_emp_salary index.


Following is the result of emp_master table based on the index specified index (idx_emp_salary).


emp_id      first_name  last_name   salary      dept_id

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

6           Sonal       Menpara     13000       1

2           Shweta      Jariwala    19300       2

9           Shwets      Jariwala    19400       2

12          Sonal       Menpara     20000       4

3           Vinay       Jariwala    35000       3

8           Khyati      Shah        49900       3

In case if the defined INDEX name does not exist in the emp_master table then it will throw an error like as shown below.


Error: no such index: idx_emp_phne

This is how we can use SQLite indexed By in Select / UPDATE / DELETE statements based on our requirements.