SQLite WHERE Clause
SQLite'sWHEREThe clause is used to specify conditions for retrieving data from one or more tables.
If the given condition is met, i.e., it is true, specific values are returned from the table. You can use the WHERE clause to filter records and only retrieve the records you need.
The WHERE clause can be used not only in SELECT statements, but also in UPDATE and DELETE statements, etc. We will learn about these in the following chapters.
Syntax
The basic syntax of a SELECT statement with a WHERE clause in SQLite is as follows:
SELECT column1, column2, columnN FROM table_name WHERE [condition]
Examples
You can also usecomparison or logical operatorsto specify conditions, such as >, <, =, LIKE, NOT, etc. Assume the COMPANY table has 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
The following example demonstrates the usage of SQLite logical operators. The following SELECT statement lists all records where AGE is greater than or equal to 25andand 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 following SELECT statement lists all records where AGE is greater than or equal to 25orand 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
The following SELECT statement lists all records where AGE is not NULL. The results show all records, meaning no record has an 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
The following SELECT statement lists all records whose NAME starts with 'Ki', with no restriction on the characters after 'Ki':
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0
The following SELECT statement lists all records whose NAME starts with 'Ki', with no restriction on the characters after 'Ki':
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*'; ID NAME AGE ADDRESS SALARY ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0
The following SELECT statement lists all records where the value of AGE is 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
The following SELECT statement lists all records where the value of AGE 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
The following SELECT statement lists all records where the value of AGE is 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
The following SELECT statement uses a SQL subquery. The subquery finds all records with the AGE field where SALARY > 65000. The subsequent WHERE clause is used together with the EXISTS operator to list all records where the AGE in the outer query exists in the results returned by the subquery:
sqlite> SELECT AGE FROM COMPANY
WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
AGE
----------
32
25
23
25
27
22
24
The following SELECT statement uses a SQL subquery. The subquery finds all records with the AGE field where SALARY > 65000. The subsequent WHERE clause is used together with the > operator to list all records where the AGE in the outer query is greater than the age in the results returned by the subquery:
sqlite> SELECT * FROM COMPANY
WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
Other Extensions