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:

ID NAME SUBJECT
1 Jim English
2 John Geology
3 Watson French
4 Holmes Chemistry
5 Tony Physics
SELECT * FROM TEACHERS
WHERE ID > 2;

Output:

ID NAME SUBJECT
3 Watson French
4 Holmes Chemistry
5 Tony Physics

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:

ID NAME SUBJECT
1 Jim English
2 John Geology
3 Watson French
4 Holmes Chemistry
5 Tony Physics
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:

ID NAME SUBJECT
1 Jim English
2 Sam Geology
3 Watson French
4 Holmes Chemistry
5 Tony Physics

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

ID NAME SUBJECT
1 Jim English
2 John Geology
3 Watson French
4 Holmes Chemistry
5 Tony Physics
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:

 

ID NAME SUBJECT
1 Jim English
2 John Geology
3 Watson French
4 Holmes Chemistry

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

ID NAME SUBJECT
1 Jim English
2 John Geology
3 Watson French
4 Holmes Chemistry
5 Tony Physics
SELECT * FROM TEACHERS
WHERE ID IN (2, 4);

Output:

ID NAME SUBJECT
2 John Geology
4 Holmes Chemistry

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:

ID NAME SUBJECT
1 Jim English
2 John Geology
3 Watson French
4 Holmes Chemistry
5 Tony Physics
SELECT * FROM TEACHERS
WHERE ID NOT IN (2, 4);

Output:

ID NAME SUBJECT
1 Jim English
3 Watson French
5 Tony Physics

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