SQLite: IN Condition
This SQLite tutorial explains how to use the SQLite IN condition with syntax and examples.
Description
The SQLite IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
Syntax
The syntax for the IN condition in SQLite is:
expression IN (value1, value2, .... value_n);
OR
expression IN (subquery);
Parameters or Arguments
- expression
- A value to test.
- value1, value2, ... value_n
- The values to test against expression.
- subquery
- This is a SELECT statement whose result set will be tested against expression. If any of these values matches expression, then the IN condition will evaluate to true.
Note
- The SQLite IN condition will return the records where expression is value1, value2..., or value_n.
- The SQLite IN condition is also called the SQLite IN operator.
Example - With Character
Let's look at a SQLite IN condition example using character values.
The following is a SQLite SELECT statement that uses the IN condition to compare character values:
SELECT * FROM employees WHERE first_name IN ('Sarah', 'Jane', 'Heather');
This SQLite IN condition example would return all rows from the employees table where the first_name is either 'Sarah', 'Jane', or 'Heather'. Because the * is used in the SELECT, all fields from the employees table would appear in the result set.
The above IN example is equivalent to the following SELECT statement:
SELECT * FROM employees WHERE first_name = 'Sarah' OR first_name = 'Jane' OR first_name = 'Heather';
As you can see, using the SQLite IN condition makes the statement easier to read and more efficient.
Example - With Numeric
Next, let's look at a SQLite IN condition example using numeric values.
For example:
SELECT * FROM employees WHERE employee_id IN (1, 2, 3, 4);
This SQLite IN condition example would return all employees where the employee_id is either 1, 2, 3, or 4.
The above IN example is equivalent to the following SELECT statement:
SELECT * FROM employees WHERE employee_id = 1 OR employee_id = 2 OR employee_id = 3 OR employee_id = 4;
Example - Using NOT operator
Finally, let's look at an IN condition example using the NOT operator.
For example:
SELECT * FROM employees WHERE first_name NOT IN ('Sarah', 'Jessica');
This SQLite IN condition example would return all rows from the employees table where the first_name is not Sarah or Jessica. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.
Advertisements