SQL IN Operator

SQL IN Syntax

SELECT column_name1, column_name2 FROM table_name 
WHERE column_name IN (value1,value2,...valueN);

Note

• Value1, Value2, ...ValueN must be the same datatype and It cannot be different.
• These are the values to test against expression. If any of these values matches expression, then the IN condition will evaluate to true.

Sample Database Table - Books

SQL IN Operator Example

The following SQL SELECT statement selects the all Books with a AuthorName of "Nirmala" or "Hanumanthan" or "Ranjani Mai", in the "Books" table:

SELECT * FROM Books 
WHERE 
AuthorName IN('Nirmala', 'Hanumanthan', 'Ranjani Mai');

The result of above query is:

Also we can write the above query using OR operator, which will be risky task while increasing the conditions.

SELECT * FROM Books 
WHERE 
AuthorName = 'Nirmala' OR AuthorName = 'Hanumanthan' OR AuthorName = 'Ranjani Mai';

SQL NOT IN Operator Example

The SQL IN condition can use with the SQL NOT operator.

To display the Books outside the list values of the previous example, use NOT IN:

SELECT * FROM Books 
WHERE 
AuthorName NOT IN('Hari Krishnan', 'Nirmala', 'Hanumanthan', 'Ranjani Mai');

The result of above query is:

SQL IN Operator Example With Numbers

The following SQL SELECT statement selects the all Books with a BookId of "3" or "6" or "11" or "12", in the "Books" table:

SELECT * FROM Books 
WHERE 
BookID IN(3, 6);

The result of above query is: