SQLite: AND Condition
This SQLite tutorial 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) 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'.
Advertisements