sqlite
  1. sqlite-where
PreviousNext

SQLite Clauses/Conditions - WHERE

The WHERE clause is a SQLite clause used to specify a condition while retrieving data from one or more tables in a database. It allows you to retrieve only the data that meets a specific condition or set of conditions.

Syntax

The basic syntax of a SELECT statement with WHERE clause is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The condition parameter is the expression which the row or rows in the table must evaluate to in order to be selected for retrieval.

Example

Suppose we have a table called students with columns student_id, name, age, and gender. We want to retrieve all the records for male students between the ages of 18 and 25. We can use the WHERE clause to specify this condition.

SELECT *
FROM students
WHERE gender = 'male' AND age >= 18 AND age <= 25;

Output

The output of the above example would be all the records for male students between the ages of 18 and 25.

student_id |   name   | age | gender
-----------|----------|-----|-------
     1     |  John    |  22 |  male
     2     |  James   |  20 |  male
     4     |  Michael |  24 |  male

Explanation

In the example above, we use the SELECT statement to retrieve data from a table called students. We use the WHERE clause to specify the condition that the gender must be male and age should be between 18 and 25.

The AND operator is used to combine multiple conditions. It means that both conditions must be true for a record to be selected.

We select all columns by using * after the SELECT keyword.

Use

The WHERE clause is used with the SELECT statement to filter data based on specified conditions. It allows us to retrieve only the data that meets a specific condition or set of conditions.

Some common use cases include:

  • Retrieving data based on specific criteria.
  • Filtering data based on a range of values.
  • Combining multiple conditions to retrieve more specific data.

Important Points

  • SQLite WHERE clause is used to filter data from one or more tables
  • It is a mandatory clause in SELECT statements that contains conditions that must be satisfied in order for a specific set of results to be returned.
  • The WHERE clause can be used with various operators. Some of the common ones include = (equals), < (less than), > (greater than), <= (less than or equal to), >= (greater than or equal to), <> (not equal to), and IN (to specify a range of values).
  • Be careful when combining multiple conditions together with the AND or OR operators, as it can affect the result set.

Summary

In this tutorial, we learned about the WHERE clause in SQLite and how it is used to filter data based on specific conditions. We saw an example of using the WHERE clause to retrieve records for male students between the ages of 18 and 25. The WHERE clause is used in combination with the SELECT statement to refine the data we want to retrieve from a database.

Delete QueryAND
Published on: