- SQLite Tutorial
- SQLite - Home
- SQLite - Overview
- SQLite - Installation
- SQLite - Commands
- SQLite - Syntax
- SQLite - Data Type
- SQLite - CREATE Database
- SQLite - ATTACH Database
- SQLite - DETACH Database
- SQLite - CREATE Table
- SQLite - DROP Table
- SQLite - INSERT Query
- SQLite - SELECT Query
- SQLite - Operators
- SQLite - Expressions
- SQLite - WHERE Clause
- SQLite - AND & OR Clauses
- SQLite - UPDATE Query
- SQLite - DELETE Query
- SQLite - LIKE Clause
- SQLite - GLOB Clause
- SQLite - LIMIT Clause
- SQLite - ORDER By Clause
- SQLite - GROUP By Clause
- SQLite - HAVING Clause
- SQLite - DISTINCT Keyword
- Advanced SQLite
- SQLite - PRAGMA
- SQLite - Constraints
- SQLite - JOINS
- SQLite - UNIONS Clause
- SQLite - NULL Values
- SQLite - ALIAS Syntax
- SQLite - Triggers
- SQLite - Indexes
- SQLite - INDEXED By Clause
- SQLite - ALTER Command
- SQLite - TRUNCATE Command
- SQLite - Views
- SQLite - Transactions
- SQLite - Subqueries
- SQLite - AUTOINCREMENT
- SQLite - Injection
- SQLite - EXPLAIN
- SQLite - VACUUM
- SQLite - Date & Time
- SQLite - Useful Functions
- SQLite Interfaces
- SQLite - C/C++
- SQLite - Java
- SQLite - PHP
- SQLite - Perl
- SQLite - Python
- SQLite Useful Resources
- SQLite - Quick Guide
- SQLite - Useful Resources
- SQLite - Discussion
SQLite - INDEXED BY Clause
The "INDEXED BY index-name" clause specifies that the named index must be used in order to look up values on the preceding table.
If index-name does not exist or cannot be used for the query, then the preparation of the SQLite statement fails.
The "NOT INDEXED" clause specifies that no index shall be used when accessing the preceding table, including implied indices created by UNIQUE and PRIMARY KEY constraints.
However, the INTEGER PRIMARY KEY can still be used to look up entries even when "NOT INDEXED" is specified.
Syntax
Following is the syntax for INDEXED BY clause and it can be used with DELETE, UPDATE or SELECT statement.
SELECT|DELETE|UPDATE column1, column2... INDEXED BY (index_name) table_name WHERE (CONDITION);
Example
Consider table COMPANY We will create an index and use it for performing INDEXED BY operation.
sqlite> CREATE INDEX salary_index ON COMPANY(salary); sqlite>
Now selecting the data from table COMPANY you can use INDEXED BY clause as follows −
sqlite> SELECT * FROM COMPANY INDEXED BY salary_index WHERE salary > 5000;
This will produce the following result.
ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 7 James 24 Houston 10000.0 2 Allen 25 Texas 15000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
To Continue Learning Please Login
Login with Google