Latest web development tutorials

SQLite Where clause

SQLite'sWHERE clause to specify to obtain data from one or more tables in the conditions.

If you meet a given condition is true (true), from table to return a specific value. You can use a WHERE clause to filter the records, just get the required records.

WHERE clause is used not only in a SELECT statement, it can also be used in UPDATE, DELETE statement, and so on, which we will learn in subsequent chapters.

grammar

The basic syntax of a SELECT statement with a WHERE clause in SQLite as follows:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

Examples

You can also use comparison or logical operators specified criteria, such as>, <, =, LIKE, NOT, and so on. Suppose 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 AGE 25and greater than or equal greater than or equal wages for all records 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 AGE 25or greater than or equal greater than or equal wages for all records 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 the AGE is not NULL for all the records, all the records showed, it means there is no record of AGE is 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

Character following SELECT statement lists NAME to 'Ki' all records began, 'Ki' is no limit after:

sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
6 Kim 22 South-Hall 45000.0

Character following SELECT statement lists NAME to 'Ki' all records began, 'Ki' is no limit after:

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 the records AGE value of 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 the values ​​AGE is neither 25 nor 27 of all records:

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 the values ​​AGE all records 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 SQL subqueries, subquery find SALARY> AGE field for all records with 65,000, behind the WHERE clause is used in conjunction with the EXISTS operator, lists outer query AGE presence in the sub-query returns results of all the records:

sqlite> SELECT AGE FROM COMPANY 
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY> 65000);
AGE
----------
32
25
twenty three
25
27
twenty two
twenty four

The following SELECT statement uses SQL subqueries, subquery find SALARY> AGE field for all records with 65,000, behind the WHERE clause and> operators are used together, the results of the query returns a list of the outer query is greater than the sub-AGE All records of the age:

sqlite> SELECT * FROM COMPANY 
        WHERE AGE> (SELECT AGE FROM COMPANY WHERE SALARY> 65000);
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0