The SQL "IN" condition helps reduce the need to use multiple SQL "OR" conditions. The SQL "IN" condition can be used in any valid SQL statement - SQL SELECT statement, SQL INSERT statement, SQL UPDATE statement, or SQL DELETE statement.
The syntax for the "IN" condition is:
expression in (value1, value2, .... value_n);
The SQL "IN" condition will return the records where expression is value1, value2..., or value_n.
SQL "IN" Condition - Character example
The following is an SQL SELECT statement that uses the IN condition to compare character values:
SELECT *
FROM suppliers
WHERE supplier_name in ('IBM', 'Hewlett Packard', 'Microsoft');
This SQL "IN" condition example would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.
It is equivalent to the following statement:
SELECT * FROM suppliers WHERE supplier_name = 'IBM' OR supplier_name = 'Hewlett Packard' OR supplier_name = 'Microsoft';
As you can see, using the SQL "IN" condition makes the statement easier to read and more efficient.
SQL "IN" Condition - Numeric example
You can also use the SQL "IN" condition with numeric values.
For example:
SELECT * FROM orders WHERE order_id in (10000, 10001, 10003, 10005);
This SQL "IN" condition example would return all orders where the order_id is either 10000, 10001, 10003, or 10005.
It is equivalent to the following statement:
SELECT * FROM orders WHERE order_id = 10000 OR order_id = 10001 OR order_id = 10003 OR order_id = 10005;
SQL "IN" Condition - Using the NOT operator
The SQL "IN" condition can also be combined with the SQL NOT operator.
For example,
SELECT * FROM suppliers WHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');
This SQL example would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.
