Filter

#Introduction

You already know the basic syntax of the select statement and how to retrieve columns from one table in your database, but very often you don’t need all records from a table.

The SQL where clause allows you to filter your results.

This clause introduces certain conditions, like:

• quantity < 100
• price between 100 and 500
• customer_name = 'John Smith'

#Sample Database

The Sakila sample database is a fictitious database designed to represent a DVD rental store. The tables of the database include film, film_category, actor, customer, rental, payment and inventory among others.

Download sakila database file from this link: sakila.db

#where

The select statement returns all rows from one or more columns in a table.

To retrieve rows that satisfy a specified condition, you use a where clause.

select <columns>
from <table>
where <condition>
order by <column>;

In this syntax,

• You place the where clause right after the from clause of the select statement.
• The where clause uses the condition to filter the rows returned from the select clause.
• The condition is a boolean expression that evaluates to true, false, or unknown.

The query returns only rows that satisfy the condition in the where clause.

In other words, the query will include only rows that cause the condition to evaluate to true in the result set.

SQLite evaluates the where clause after the from clause, but before the select and order by clause:

If you use column aliases in the select clause, you cannot use them in the where clause.

Besides the select statement, you can use the where clause in the update and delete statement to specify rows to update and delete.

To form the condition in the where clause, you use comparison and logical operators:

#Tasks

Let’s practice with some examples of using the where clause.

We will use the customer table from the sample database for demonstration.

select first_name, last_name
from customer
where first_name = 'JAMIE'

select first_name, last_name
from customer
where first_name = 'JAMIE' and last_name = 'RICE'