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;
---------- ---------- ---------- ---------- ----------
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;
---------- ---------- ---------- ---------- ----------
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;
---------- ---------- ---------- ---------- ----------
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%';
---------- ---------- ---------- ---------- ----------
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 *';
---------- ---------- ---------- ---------- ----------
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);
---------- ---------- ---------- ---------- ----------
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);
---------- ---------- ---------- ---------- ----------
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;
---------- ---------- ---------- ---------- ----------
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);