SQLite - WHERE Clause

In SQLite the WHERE clause specifies the condition on the columns of the tables.

The WHERE clause can not only be used in SELECT statement, but it can also used in DELETE, UPDATE statement. We will learn about all these statements in next sections.

SQLite WHERE clause Syntax

The WHERE clause is used to extract only those records that fulfill a specified criterion. Here is the basic syntax:

SELECT column_name1, column_name2, column_nameN 
FROM table_name
WHERE [condition]

Conditions Used In Where Clause

Condition	Details
=	equals
>	greater than
<	Less than
>=	greater than or equal to
<=	less than or equal to
<>	not equal to
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

Example:

So let's consider we have created SCHOOL database. And we have created a STUDENTS table in this database.

The STUDENTS table have five columns (ID, NAME, SURNAME, AGE, ADDRESS).

The STUDENTS table also have some data inside it as shown below:

ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
3           Patric      Rossman     19          Essen
4           Noor        Khan        22          Bonn
5           Julia       Tesar       18          Berlin
6           Tim         Netten      20          Frankfurt
7           John        Mevric      17          Wuppertal

The following SQLite statement selects the columns from the STUDENTS table where the NAME is equal to Mark:

sqlite> SELECT * FROM STUDENTS WHERE NAME='Mark';
ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich

The following SQLite statement selects the columns from the STUDENTS table where the AGE is >= 20:

sqlite> SELECT * FROM STUDENTS WHERE AGE>=20;
ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
4           Noor        Khan        22          Bonn
6           Tim         Netten      20          Frankfurt

The following SQLite statement selects the columns from the STUDENTS table where the AGE is BETWEEN 20 AND 22:

sqlite> SELECT * FROM STUDENTS WHERE AGE BETWEEN 20 AND 22;
ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
1           Mark        Osaka       20          Munich
2           Tom         white       21          Cologne 
4           Noor        Khan        22          Bonn
6           Tim         Netten      20          Frankfurt

The LIKE operator is used to search for a specified pattern in a column.

Syntax:

sqlite> SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

The following SQLite statement selects the columns from the STUDENTS table where the NAME starts with 'Jo', does not matter what comes after 'Jo':

sqlite> SELECT * FROM STUDENTS WHERE NAME LIKE 'Jo%';
ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
7           John        Mevric      17          Wuppertal

The following SQLite statement selects the columns from the STUDENTS table where the AGE value is either 18 or 21:

sqlite> SELECT * FROM STUDENTS WHERE AGE IN ( 18, 21 );
ID          NAME        SURNAME     AGE         ADDRESS
----------  ----------  ----------  ----------  ----------
2           Tom         white       21          Cologne 
5           Julia       Tesar       18          Berlin

Android Books To Learn Mobile Apps Programming

SqLite Tutorial

SqLite - HOME SqLite - Intro SqLite - Installation SqLite - Syntax SqLite - Data Types SqLite - Create DB SqLite - Create Table SqLite - Drop Table SqLite - Select SqLite - Distinct SqLite - Where SqLite - And & Or SqLite - Order By SqLite - Insert Into SqLite - Update SqLite - Delete SqLite - Injection SqLite - Select Top SqLite - Like SqLite - Wildcards SqLite - In SqLite - Between SqLite - Aliases SqLite - Joins SqLite - Inner Join SqLite - Left Join SqLite - Right Join SqLite - Full Join SqLite - Union SqLite - Select Into SqLite - Insert Into Select SqLite - Constraints SqLite - Not Null SqLite - Unique SqLite - Primary Key SqLite - Foreign Key SqLite - Check SqLite - Default SqLite - Create Index SqLite - Drop SqLite - Alter SqLite - Auto Increment SqLite - Views SqLite - Dates SqLite - Null Values SqLite - Null Functions SqLite - Data Types SqLite - DB Data Types

SqLite Functions

SqLite - Functions SqLite - Avg() SqLite - Count() SqLite - First() SqLite - Last() SqLite - Max() SqLite - Min() SqLite - Sum() SqLite - Group By SqLite - Having SqLite - Ucase() SqLite - Lcase() SqLite - Mid() SqLite - Len() SqLite - Round() SqLite - Now() SqLite - Format()