WHERE Clause in SQLite

SQLite WHERE Clause
To specify a condition for fetching only the necessary data from a table or tables, the SQLite WHERE clause is used. It is generally used with SELECT, UPDATE and DELETE statements.

Syntax:

SELECT column_1, column_2, column_N   
FROM table_name   
WHERE expression;   

Example 1: Using WHERE clause with the SELECT statement.
TEACHERS Table:

IDNAMESUBJECT
1JimEnglish
2JohnGeology
3WatsonFrench
4HolmesChemistry
5TonyPhysics
SELECT * FROM TEACHERS
WHERE ID > 2;

Output:

IDNAMESUBJECT
3WatsonFrench
4HolmesChemistry
5TonyPhysics

Explanation:
The “TEACHERS” is an already existing table. Here we are selecting those rows of the table where the value in the ID column is greater than 2.

Example 2: Using WHERE clause with the UPDATE statement.
TEACHERS Table:

IDNAMESUBJECT
1JimEnglish
2JohnGeology
3WatsonFrench
4HolmesChemistry
5TonyPhysics
UPDATE TEACHERS 
SET NAME = 'Sam' WHERE ID = 2;

Explanation:
Here we have updated the value of the NAME column in an already existing table “TEACHERS” where ID is 2. To verify the table data execute the below query.

SELECT * FROM TEACHERS;

Output:

IDNAMESUBJECT
1JimEnglish
2SamGeology
3WatsonFrench
4HolmesChemistry
5TonyPhysics

Example 3: Using WHERE clause with the DELETE statement.
TEACHERS Table:

IDNAMESUBJECT
1JimEnglish
2JohnGeology
3WatsonFrench
4HolmesChemistry
5TonyPhysics
DELETE FROM TEACHERS 
WHERE NAME = ‘Tony’;

Explanation:
Here we have deleted the row from an already existing table “TEACHERS” where NAME is ‘Tony’. To verify the table data execute the below query.

SELECT * FROM TEACHERS;

Output:

 

IDNAMESUBJECT
1JimEnglish
2JohnGeology
3WatsonFrench
4HolmesChemistry

Example 4: Using WHERE clause with the IN operator.
TEACHERS Table:

IDNAMESUBJECT
1JimEnglish
2JohnGeology
3WatsonFrench
4HolmesChemistry
5TonyPhysics
SELECT * FROM TEACHERS
WHERE ID IN (2, 4);

Output:

IDNAMESUBJECT
2JohnGeology
4HolmesChemistry

Explanation:
The “TEACHERS” is an already existing table. Here we are fetching those rows of the table where the value in the ID column is either 2 or 4.

Example 5: Using WHERE clause with the NOT IN operator.
TEACHERS Table:

IDNAMESUBJECT
1JimEnglish
2JohnGeology
3WatsonFrench
4HolmesChemistry
5TonyPhysics
SELECT * FROM TEACHERS
WHERE ID NOT IN (2, 4);

Output:

IDNAMESUBJECT
1JimEnglish
3WatsonFrench
5TonyPhysics

Explanation:
The “TEACHERS” is an already existing table. Here we are fetching those rows of the table where the value in the ID column is neither 2 nor 4 (where the value in the ID column is either 1, 3, or 5).

Please Share