YouTipSqlite Where Clause
The **WHERE** clause in SQLite is used to specify conditions for fetching data from one or more tables.
If the given condition is met (true), specific values are returned from the table. You can use the WHERE clause to filter records and retrieve only the necessary ones.
The WHERE clause is not only used in SELECT statements but also in UPDATE, DELETE statements, etc., which we will learn in subsequent chapters.
The basic syntax of a SELECT statement with a WHERE clause in SQLite is as follows:
SELECT column1, column2, columnN FROM table_name WHERE
You can also specify conditions using (#), such as >, 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 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 where NAME starts with 'Ki', with no restrictions on 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 where NAME starts with 'Ki', with no restrictions on 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 an SQL subquery. The subquery finds all records with the AGE field where SALARY > 65000. The subsequent WHERE clause, used with the EXISTS operator, lists all records from the outer query where the 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. The subquery finds all records with the AGE field where SALARY > 65000. The subsequent WHERE clause, used with the > operator, lists all records from the outer query where the AGE is greater than the age 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