SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause. The IN function helps reduce the need to use multiple OR conditions. The SQL IN clause allows you to specify discrete values in your SQL WHERE search criteria. The syntax for using the IN keyword is as follows:

SELECT field(s) FROM table_name WHERE fields IN (v1,v2,v3...); 

Inside the query statement itself, the word “IN” replaces the (=) operator after the WHERE declarative and slightly alters the meaning as well. Instead of listing a single value, you may list multiple values and SQL will retrieve the results for each value listed. There are two uses of the IN keyword in SQL first one is the Simple IN Keyword with Where Condition and the second way with NOT IN. To illustrate the usage of the IN command we are going to use the EMP table.

SELECT * FROM emp WHERE deptno IN (10,20,30); 

The results provide a list of all records of each of the employee we have listed inside the IN clause (10,20,30). This is a great way to query for all records made by a handful of different deptno as we can see everything these particular employee have ordered thus far.

On the Other Hand SQL NOT IN eliminate a list of specific values from the result set. To illustrate the usage of the Not IN into command we are going to use the EMP table.

SELECT * FROM emp WHERE deptno Not IN (10,20,30);