SQLite WHERE Clause
SQLite's WHERE clause is used to specify conditions for retrieving data from one or multiple tables.
If the given condition is true, specific values are returned from the table. You can use the WHERE clause to filter records and obtain only the necessary records.
The WHERE clause is not only used in SELECT statements but also in UPDATE, DELETE statements, and more, which we will learn about in subsequent 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]
Example
You can also specify conditions using comparison or logical operators such as >, <, =, LIKE, NOT, etc. Suppose 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 use of SQLite logical operators. The SELECT statement below lists all 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 following SELECT statement lists all 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
The following SELECT statement lists all records where AGE is not NULL, showing all records, which means none of the records have 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 where NAME starts with 'Ki', with no restrictions on the characters following '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 where NAME starts with 'Ki', with no restrictions on the characters following '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 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
The following SELECT statement lists all records where the 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
The following SELECT statement lists all records where the AGE value 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 an SQL subquery, where the subquery finds all records with the AGE field where SALARY > 65000, and the subsequent WHERE clause, used with the EXISTS operator, lists all records from the outer query where AGE 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 an SQL subquery, where the subquery finds all records with the AGE field where SALARY > 65000, and the subsequent WHERE clause, used with the > operator, lists all records from the outer query where AGE is greater than the ages 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