SQLite: Combining the AND and OR Conditions

This SQLite post explains how to use the AND condition and the OR condition together in a SQLite query with syntax and examples.

Description

The SQLite AND condition and OR condition can be combined in a SELECT, INSERT, UPDATE, or DELETE statement.

When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition. (Just like when you were learning the order of operations in Math class!)

Syntax

The syntax for the AND condition and OR condition together in SQLite is:

WHERE condition1
AND condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n

The conditions that are evaluated to determine if the records will be selected.

Note

• The SQLite AND & OR conditions allow you to test multiple conditions.
• Don't forget the order of operation parentheses!

Example - With SELECT Statement

Let's look at an example that combines the AND and OR conditions in a SELECT statement.

For example:

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

This AND & OR example would return all employees that have a last_name of 'Smith' and a first_name of 'Jane', and 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!

The next example takes a look at a more complex statement.

For example:

SELECT last_name, first_name
FROM employees
WHERE (first_name = 'Dave')
OR (last_name = 'Mark' AND first_name = 'Sarah')
OR (employee_id = 1 AND last_name = 'Smith');

This AND & OR example would return all last_name, and first_name values from the employees table whose the first_name is 'Dave' OR whose last_name is Mark and the first_name is 'Sarah' OR whose employee_id is 1 and last_name is 'Smith'.

Example - With INSERT Statement

This next AND & OR example demonstrates how the AND condition and OR condition can be combined in the INSERT statement.

For example:

INSERT INTO contacts
(contact_id, last_name, first_name)
SELECT employee_id, last_name, first_name
FROM employees
WHERE (last_name = 'Davis' OR last_name = 'Jones')
AND employee_id  50;

This SQLite AND and OR example would insert into the contacts table, all employee_id, last_name, and first_name records from the employees table whose last_name is either 'Davis' or 'Jones' and where the employee_id is less than 50.

Example - With UPDATE Statement

This AND & OR example shows how the AND and OR conditions can be used in the UPDATE statement.

For example:

UPDATE employees
SET department = 'Accounting'
WHERE last_name = 'Mark'
AND (employee_id = 1 OR employee_id = 2);

This SQLite AND & OR condition example would update all department values in the employees table to 'Accounting' where the last_name is 'Mark' and the employee_id is either 1 or 2.

Example - With DELETE Statement

Finally, this last AND & OR example demonstrates how the AND and OR conditions can be used in the DELETE statement.

For example:

DELETE FROM employees
WHERE employee_id > 50
AND (last_name = 'Jones' OR last_name = 'Davis');

This SQLite AND and OR condition example would delete all records from the employees table where the employee_id is greater than 5 and the last_name is either 'Jones' or 'Davis'.