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.


Search Keys

  • sql in operator
  • in sql
  • queries in sql
  • sql in clause
  • not equal in sql
  • not in sql
  • sql in statement
  • where in sql
  • using or in sql
  • in command in sql
  • in condition in sql
  • select not in sql
  • sql select where in list
  • sql value in list
  • sql in list
  • in list sql
  • in and not in sql
  • use command in sql
  • how to use in sql query
  • sql where not in list
  • in operator in sql
  • sql not in list
  • sql in range
  • sql in command
  • in operator sql
  • where in select

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
7 SQL for students 123.45 2014 Optimization Vidyavathi
8 MySql Database Internals 195 2014 Security Ramanathan
9 Complete Guide To No-SQL 185 2010 Programming Geetha
10 Programming With Sql Server T-Sql 100 2012 Programming Nirmala
11 Oracle Database Internals 200 2014 Programming Hari Krishnan
12 MySql Concurrency 150 2012 Administration Hari Krishnan

SQL IN Operator Example

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


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

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
2 Head First SQL Brain On SQL 155 2006 Security Ranjani Mai
4 MySql for professionals 84.22 2009 Administration Nirmala
9 Complete Guide To No-SQL 185 2010 Programming Geetha
10 Programming With Sql Server T-Sql 100 2012 Programming 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 = 'Geetha' 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', 'Geetha', 'Ranjani Mai');

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
1 Microsoft SQL Server 2012 125 2014 Performance Hanumanthan
5 Getting Started With SQL 90 2012 Security Vinoth Kumar
7 SQL for students 123.45 2014 Optimization Vidyavathi
8 MySql Database Internals 195 2014 Security Ramanathan

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, 11, 12);

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
11 Oracle Database Internals 200 2014 Programming Hari Krishnan
12 MySql Concurrency 150 2012 Administration Hari Krishnan

Search Keys

  • sql not in statement
  • sql in example
  • not in sql example
  • sql query in operator
  • sql in and not in
  • sql query in clause
  • sql select where in list of values
  • t sql in statement
  • sql server in operator
  • sql in query example
  • in keyword sql
  • operators in pl sql
  • sql not in clause
  • sql not in example
  • sql in operator example
  • sql in condition
  • between in sql
  • select in sql
  • not between in sql
  • not in operator in sql
  • how to use between in sql
  • list in sql
  • range query in sql
  • range operator in sql
  • multiple where in sql
  • sql where list
  • sql range