SQLite: AND Condition

This SQLite post explains how to use the SQLite AND condition with syntax and examples.

Description

The SQLite AND Condition (also called the AND Operator) is used to test two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the AND Condition in SQLite is:

WHERE condition1
AND condition2
...
AND condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n

All of the conditions that must be met for the records to be selected.

Note

• The SQLite AND condition allows you to test 2 or more conditions.
• The SQLite AND condition requires that all of the conditions (ie: condition1, condition2, condition_n) be must be met for the record to be included in the result set.

Example - With SELECT Statement

Let's look at some examples that show how to use the AND condition in SQLite.

The first SQLite AND condition query involves a SELECT statement with 2 conditions.

For example:

SELECT *
FROM employees
WHERE last_name = 'Smith'
AND employee_id = 50;

This SQLite AND example would return all employees that have a last_name of 'Smith' and have a employee_id less than or equal to 50. Because the * is used in the SELECT statement, all fields from the employees table would appear in the result set.

Example - JOINING Tables

Our next SQLite AND example shows how the AND condition can be used to join multiple tables in a SELECT statement.

For example:

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

Though the above SQL works just fine, you would more traditionally write this SQL as follows using a proper INNER JOIN.

For example:

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

This SQLite AND condition example would return all rows where the employee_id is greater than 100. And the employees and positions tables are joined on the position_id field. You will notice that all of the fields are prefixed with the table names (ie: employees.employee_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the employees and the positions tables.

In this case, the result set would only display the employee_id, last_name, and title fields (as listed in the first part of the SELECT statement.).

Example - With INSERT Statement

This next SQLite AND example demonstrates how the AND condition can be used 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 employee_id  20
AND employee_id  100;

This SQLite AND condition example would insert into the contacts table, all employee_id, last_name, and first_name values from the employees table where the employee_id is greater than 20 and less than 100.

Example - With UPDATE Statement

This SQLite AND condition example shows how the AND condition can be used in the UPDATE statement.

For example:

UPDATE employees
SET employee_id = 1
WHERE last_name = 'Smith'
AND first_name = 'Jane';

This SQLite AND condition example would update all employee_id values in the employees table to 1 where the employee's last_name is 'Smith' and first_name is 'Jane'.

Example - With DELETE Statement

Finally, this last SQLite AND example demonstrates how the AND condition can be used in the DELETE statement.

For example:

DELETE FROM employees
WHERE employee_id  20
AND first_name = 'Sarah';

This SQLite AND condition example would delete all records from the employees table where the employee_id is greater than 20 and the first_name is 'Sarah'.