SQL > SQL Commands >
The IN operator in SQL filters the result set based on a list of discrete values. The list of discrete values can be simply be listed out or is provided by a separate SELECT statement (this is called a subquery).
The IN operator is always used with the WHERE clause.
Below is the syntax for the IN operator when the possible values are listed out directly.
WHERE "column_name" IN ('value1', 'value2', ...);
The number of values in the parenthesis can be one or more, with each values separated by comma. Values can be numerical or string characters. If there is only one value inside the parenthesis, this commend is equivalent to,
WHERE "column_name" = 'value1'
The syntax for the IN operator in a subquery construct is as follows:
WHERE "column_name" IN ( [SELECT STATEMENT] );
Please note that the IN operator cannot be used if the filtering criteria is a continuous range. For example, if we are looking for any value that is between 0 and 1, we cannot use the IN operator because it is not possible to list every possible value between 0 and 1.
We use the following table for our example.
To select all records for the Los Angeles and the San Diego stores in Table Store_Information, we key in,
WHERE Store_Name IN ('Los Angeles', 'San Diego');
For an example of the IN operator used in conjunction with a separate SELECT statement, please see the Subquery section.
For these exercises, assume we have a table called Users with the following data:
1. Which of the following SQL statement is valid? (There can be more than one answer)
2. How many records will be returned by the following query?
3. How many records will be returned by the following query?