SQL IN Operator
The following snippet shows the syntax for the IN operator.
value_set:
{
(expression[, ...])
| (subquery)
| UNNEST(array_expression)
}
The function accepts the following parameters:
- search_value – this defines an expression that is compared to the given set of values.
- value_set – the set of values from which the search_value is compared.
- subquery – describes a subquery that returns a single column. If no values are returned, the value set is empty.
- UNNEST(array) – returns a column of values from a given array value.
The function then returns a Boolean value. It returns TRUE if an equal value exists in the given set and FALSE if otherwise.
Examples
The following example shows how to use the IN operator with column data. Suppose we have a table with sample data as shown:
We can use the IN operator to select the records if the country is MySQL or PostgreSQL.
*
FROM
DATABASES
WHERE
SERVER_NAME IN('MySQL', 'PostgreSQL');
The query above should display the matching rows as shown:
Using the IN operator, we can also check if a value exists in a given column. An example is shown below:
*
FROM
DATABASES D
WHERE
'MySQL' IN(SERVER_NAME);
In this case, we check if the value “MySQL” exists in the server_name column. The result is as shown:
It is good to keep in mind that if you provided duplicate values with the IN operator. SQL will discard the identical values and use one of them.
Conclusion
In this brief post, we explored the use of the IN operator in Standard/ANSI SQL. The IN operator allows you to check if a value exists within a set of values.
