SQLite: WHERE Clause

This SQLite post explains how to use the SQLite WHERE clause with syntax and examples.

Description

The SQLite WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the WHERE clause in SQLite is:

WHERE conditions;

Parameters or Arguments

conditions

The conditions that must be met for records to be selected.

Example - With Single condition

It is difficult to explain the syntax for the SQLite WHERE clause, so let's look at some examples.

SELECT *
FROM employees
WHERE first_name = 'Sarah';

In this SQLite WHERE clause example, we've used the WHERE clause to filter our results from the employees table. The SELECT statement above would return all rows from the employees table where the first_name is 'Sarah'. Because the * is used in the SELECT, all fields from the employees table would appear in the result set.

Example - Using AND condition

SELECT *
FROM employees
WHERE last_name = 'Jones'
AND favorite_website = 'AODBA.com';

This SQLite WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND Condition to return all employees that have a last_name of 'Jones' and a favorite_website of 'AODBA.com'.

Example - Using OR condition

SELECT employee_id, last_name, first_name
FROM employees
WHERE employee_id = 1
OR employee_id = 2;

This SQLite WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND Condition, it uses the OR Condition. In this case, this SELECT statement would return all employee_id, last_name, and first_name values from the employees table where the employee_id is 1 or 2.

Example - Combining AND & OR conditions

SELECT *
FROM employees
WHERE (last_name = 'Smith' AND first_name = 'Jane')
OR (employee_id = 1);

This SQLite WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND Condition and the OR Condition. This example would return all employees that have a last_name of 'Smith' and a first_name of 'Jane' as well as all employees that have an employee_id of 1.

The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

Example - Joining Tables

SELECT employees.employee_id, positions.title
FROM employees, positions
WHERE employees.position_id = positions.position_id;

This SQLite WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all employee_id and title values where there is a matching record in the employees and positions tables based on position_id.

Although you can use the older syntax to join your tables using the WHERE clause, it is more appropriate to use a proper INNER JOIN as follows:

SELECT employees.employee_id, positions.title
FROM employees 
INNER JOIN positions
ON employees.position_id = positions.position_id;

Learn more about SQLite joins.