Which operator is used to compare a value to a specified list of values?

Operator to Compare Many Values Together

Many technics are available that can be used to compare a value from a list of specified values. There is no limit to the use of the mind, if there is no direct method then you can develop other methods. According to research, here are some direct methods and some indirect methods.

1. Direct Methods

There are two operators that can be used to compare a list of values from a value.

• IN
• NOT IN

IN Operator

IN operator is used to compare a list of values from a value, if there is any match found then the expression will return TRUE, and if there is no match found then the expression will return FALSE.

Syntax Of IN Operator

Expression IN (value1,value2, value3, value4,...)

Suppose there are 100 players, they are 20 to 30 years old. Now we want all players whose ages are either 22, 23, 26, or 29. In this condition, you can use IN operator for all players listed with specified ages.

SELECT * from tblPlayers where age IN (22, 23, 26, 29)

The above query will return the list of all players whose ages match the specified values.

NOT IN Operator

NOT IN operator works just opposite as the IN operator, It returns TRUE of all not matching values.

Syntax Of NOT IN Operator

Expression NOT IN (value1,value2, value3, value4,...)

SELECT * from tblPlayers where age NOT IN (20, 21, 24, 25, 27, 28, 30)

The above query will return all players list whose age is not on the list of 20, 21, 24, 25, 27, 28, and 30

2. Indirect Methods

You can use multiple OR, multiple AND, or multiple both (AND, OR) to compare a list of values. See the below example.

For Example

SELECT * from tblPlayers where age=22 OR age=23 OR age=26 OR age=29

These are the ways to compare a list of values, but the important this is managing time complexity, your query must take less time to compare all values because its matters a lot when you will work on a table that contains lots of records.