SQL IN Operators

SQL IN Characters :

  1. SQL IN Operator is used to select the values from the specific range specified in where clause.
  2. SQL IN operator allows you to specify multiple values in a WHERE clause

Syntax :

Syntax for the IN Operator is as below -

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

Demo Database :

Consider the following demo table -

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Kota      |  2000.00 |
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Ram      |  21 | Karachi   |  2000.00 |
|  4 | Saurabh  |  25 | Mumbai    |  6500.00 |
|  5 | Poonam   |  29 | Bhopal    |  8500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
|  7 | Arnav    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

SQL IN Operators : Example #1

Consider below query on demo database -

SELECT * FROM EMPLOYEE
WHERE ADDRESS IN ('Delhi','Pune');

will result into 

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
+----+----------+-----+-----------+----------+

SQL IN statement will get all the employee that are having the address either pune or delhi. Alternatively we can also write the above statement using OR operator.

SQL:> SELECT * FROM EMPLOYEE
WHERE ADDRESS = 'Delhi' OR 
      ADDRESS = 'Pune'

SQL IN Operators : Example #2

We can also use the numeric value in case of SQL IN operator -

SQL> SELECT * FROM EMPLOYEE
WHERE AGE IN (21,23);

Above query will return all the employee having the age either 21 or 23 -

+----+----------+-----+-----------+----------+
| ID | ENAME    | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Raj      |  23 | Delhi     |  1500.00 |
|  3 | Ram      |  21 | Karachi   |  2000.00 |
|  6 | Komal    |  23 | Pune      |  4500.00 |
+----+----------+-----+-----------+----------+