SQL IN condition

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