Here we will learn what is indexed by in SQLite and how to use SQLite indexed by with examples.
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.
Following is the syntax of using SQLite INDEXED BY with SELECT / UPDATE / DELETE statements.
SELECT | UPDATE| DELETE column-list
INDEXED BY (index_name) table_name
WHERE (CONDITION);
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
(emp_id INTEGER PRIMARY KEY AUTOINCREMENT,
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),
(5,'Shweta','Rana',12000,3),
(6,'sonal','Menpara', 13000,1),
(7,'Yamini','Patel', 10000,2),
(8,'Khyati','Shah', 500000,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
---------- ---------- ---------- ---------- ----------
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.