Learn how to use the SQL IN condition with syntax and examples.
DESCRIPTION
The SQL IN condition is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
SYNTAX
The syntax for the SQL IN condition is:
expression IN (value1, value2, .... value_n);
Parameters or Arguments
expression is a value to test.
value1, value2..., or value_n are the values to test against expression.
NOTE
- The SQL IN condition will return the records where expression is value1, value2..., or value_n.
- The SQL IN condition is also called the SQL IN operator.
EXAMPLE - WITH CHARACTER
Let's look at an IN condition example using character values.
The following is a 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.
This IN condition example is equivalent to the following SQL 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.
EXAMPLE - WITH NUMERIC
Next, let's look at an IN condition example using 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.
This IN condition example is equivalent to the following SQL statement:
SELECT * FROM orders WHERE order_id = 10000 OR order_id = 10001 OR order_id = 10003 OR order_id = 10005;
EXAMPLE - USING NOT OPERATOR
Finally, let's look at an IN condition example using the NOT operator.
For example:
SELECT * FROM suppliers WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft');
This SQL IN condition 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.
