SQL IN Statement
The IN operator allows you to specify multiple values in a WHERE clause.
Sql IN Syntax:
SELECT (column name)FROM table_name
WHERE (column name)
IN (value1,value2,...)Script:
--first lets drop the table Test (if she exists, if not skip the drop line).
Drop table test;
create table test (id int,name varchar(20),email varchar(20),salary int ,de char(20));
--insert values into table
insert into test values (1,'Eve','[email protected]',1500,'HR');
insert into test values (2,'Jon','[email protected]',2500,'AD');
insert into test values (3,'Mike','[email protected]',3000,'AD');
insert into test values (4,'Paul','[email protected]',3200,'HR');
insert into test values (5,'Mary','[email protected]',1800,'IT');
insert into test values (6,'Jane','[email protected]',2200,'IT');select * from test ;
ID NAME EMAIL SALARY DE
---------- -------------------- -------------------- ---------- -- --------
1 Eve eve@gmail.com 1500 HR
2 Jon Jon@gmail.com 2500 AD
3 Mike mike@gmail.com 3000 AD
4 Paul paul@gmail.com 3200 HR
5 Mary mary@gmail.com 1800 IT
6 Jane jane@gmail.com 2200 ITNow let us use the IN operator.
Example :
-let's see all the names that have id equal to 1,2,3.select name from test where id in (1,2,3);
NAME
-------------------
Eve
Jon
Mike- is pretty simple, you just put your option in the parentheses as the condition and here you go.
Note:
- is not necessary that all the values that are in the IN operator to be true so that the query would work. We can throw values that don't exist in our table but we won't get any results.
Example :
select name from test where id in (1,2,333);
NAME
--------------------
Eve
Jon- we see that the query only shows two names that are related to first two values declared in the IN operator.
Example :
-we can write this query like this as well and it has the same actionselect name from test where id=1 or id=2 or id=333;
NAME
--------------------
Eve
Jon