SQLite WHERE Clause

SQLite is the serverless database engine that is used most widely. It is written in c programming language and it belongs to the embedded database family. In this article, you will be learning about the where clause and functionality of the where clause in SQLite.

Where Clause

SQLite WHERE Clause is used to filter the rows based on the given query. WHERE clause helps to find the result very effectively with some conditions in it. It is not only used with the SELECT statement but also with the UPDATE, DELETE, or INSERT statement.

• If the condition is satisfied then the desired output is fetched otherwise, it will throw an error.
• We can also use WHERE clause with several comparison and logical operators like >, <, =, IN, NOT IN, etc

Syntax:

Syntax for SQLite WHERE Clause:

SELECT column_1, column_2, column_N   
FROM table_name   
WHERE expression; 

Explanation: We use the select keyword followed by the column name, from, the tablename from which we are fetching the data and now we use the where keyword and the expression that we want to use to fetch our desired data.

Where Clause Examples

1. Using WHERE Clause with SELECT Statement.
2. Using WHERE Clause with Greater than Operator.
3. Using WHERE Clause with IN Operator.
4. Using WHERE Clause with NOT IN Operator.

1. Using WHERE Clause with SELECT Statement

We are going to use the students table and we are using the WHERE clause with it. here select statement is used to fetch the data and we are using an asterisk(*) to fetch the entire data of the selected field.

Student Table on which Operation Will Performs

Syntax:

select * from students
where stu_id = 4;

Output:

Explanation: In the above query, we can see that we are using the students table to fetch the data of student whose id is 4. So, here we have used the where condition to specify the id for which we want the result.

2. Using WHERE Clause with UPDATE statement:

In this example, we will update the existing data from the table with the help of the WHERE condition to specify which value of data we want to update.

Syntax:

UPDATE students
set fees = 60000
where stu_id = 4;

Output:

Explanation: In the above query, we have changed the fees of the student whose id is 4 changed to 6000 as we have updated the value. Here we specified the id with the help of the WHERE Clause.

3. Using WHERE Clause with > (Greater than) operator:

Let’s take one more example with > (Greater than) Operator. We simply fetch all those records where the students fees greater than 30000.

Syntax:

select * from students
where fees > 30000;

Output:

Explanation: In the above query, we are fetching the record of those students whose fess is greater than 300000. We have specified the amount of fees in WHERE Clause.

4 .Using WHERE Clause with IN operator:

We will take one more example of WHERE clause with the IN operator to fetch the data using the IN operator by specifying the condition.

Syntax:

select * from students
where stu_id in (2,4);

Output:

Explanation: In the above query, we are fetching those rows of the table where the value in the ID column is either 2 or 4 in the students table.

Conclusion:

This is how the WHERE clause is used to fetch the desired output with the condition that we specify. We have also learned how operators like IN, NOT IN, GREATER THAN and so on are used with the WHERE clause.