SQL IN Operator
The SQL IN operator allows you to specify a list of fixed values at once in a WHERE clause.
The SQL IN operator is used to help reduce the need for multiple OR conditions in a SQL statement.
It works with values of all datatypes like numbers, text, and dates.
Related Links
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
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | Microsoft SQL Server 2012 | 125 | 2014 | Performance | Hanumanthan |
2 | Head First SQL Brain On SQL | 155 | 2006 | Security | Ranjani Mai |
3 | PHP And MySQL Bible | 140 | 2010 | Database | Hari Krishnan |
4 | MySql for professionals | 84.22 | 2009 | Administration | Nirmala |
5 | Getting Started With SQL | 90 | 2012 | Security | Vinoth Kumar |
6 | The Gurus Guide To SQL Server | 190 | 2013 | Programming | Hari Krishnan |
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:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | Microsoft SQL Server 2012 | 125 | 2014 | Performance | Hanumanthan |
2 | Head First SQL Brain On SQL | 155 | 2006 | Security | Ranjani Mai |
4 | MySql for professionals | 84.22 | 2009 | Administration | Nirmala |
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:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
5 | Getting Started With SQL | 90 | 2012 | Security | Vinoth Kumar |
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:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
3 | PHP And MySQL Bible | 140 | 2010 | Database | Hari Krishnan |
6 | The Gurus Guide To SQL Server | 190 | 2013 | Programming | Hari Krishnan |
Related Links