SQL: “IN“ Condition

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 notwant, as opposed to the values that you do want.