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 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.