With the IN operator, you can compare a column or the outcome of a column expression against a list of values.
Using the IN operator is a simpler way of writing a series of OR conditions.
Instead of writing empno = 7002 OR empno = 7004 OR empno = 7008, you simply use an IN-list.
select empno, ename, init from emp where empno in (7002,7004,7008);
IN operator also has its own built-in negation option.
The following example lists all course registrations that do not have an evaluation value of 3, 4, or 5.
select * from registrations where evaluation NOT IN (3,4,5);
The following four expressions are logically equivalent:
where evaluation NOT in (3,4,5) where NOT evaluation in (3,4,5) where NOT (evaluation=3 OR evaluation=4 OR evaluation=5) where evaluation<>3 AND evaluation<>4 AND evaluation<>5
IN operator requires that that all of the values you specify between the parentheses must have the same or relevant data type.
SQL> SQL> drop table emp; Table dropped.-- w ww. j a va 2 s. co m SQL> create table emp( 2 empno NUMBER(4) primary key, 3 ename VARCHAR2(8) not null , 4 init VARCHAR2(5) not null , 5 job VARCHAR2(8) , 6 mgr NUMBER(4) , 7 bdate DATE not null , 8 msal NUMBER(6,2) not null , 9 comm NUMBER(6,2) , 10 deptno NUMBER(2) default 10) ; SQL> SQL> insert into emp values(7001,'SMITH','N', 'TRAINER', 7902,date '1975-12-17', 1800 , NULL, 20); SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20', 1600, 300, 30); SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02', 1250, 500, 10); SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02', 2975, NULL, 20); SQL> insert into emp values(7005,'BROWN','P', 'SALESREP',7006,date '1976-09-28', 1250, 1400, 30); SQL> insert into emp values(7006,'BLAKE','R', 'MANAGER', 7009,date '1973-11-01', 2850, NULL, 10); SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09', 2450, NULL, 10); SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26', 3000, NULL, 20); SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17', 5000, NULL, 10); SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28', 1500, 0, 30); SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30', 1100, NULL, 20); SQL> insert into emp values(7012,'JONES','R', 'ADMIN', 7006,date '1979-10-03', 8000, NULL, 30); SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13', 3000, NULL, 20); SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN', 7007,date '1972-01-23', 1300, NULL, 10); SQL> SQL> drop table registrations; Table dropped. SQL> create table registrations( 2 attendee NUMBER(4) not null, 3 course VARCHAR2(6) , 4 begindate DATE not null, 5 evaluation NUMBER(1) check (evaluation in (1,2,3,4,5))) ; SQL> SQL> insert into registrations values (7002,'SQL',date '1999-04-12',4 ); SQL> insert into registrations values (7934,'SQL',date '1999-04-12',5 ); SQL> insert into registrations values (7006,'SQL',date '1999-04-12',4 ); SQL> insert into registrations values (7011,'SQL',date '1999-04-12',2 ); SQL> insert into registrations values (7008,'SQL',date '1999-10-04',NULL); SQL> insert into registrations values (7009,'SQL',date '1999-10-04',3 ); SQL> insert into registrations values (7902,'SQL',date '1999-10-04',4 ); SQL> insert into registrations values (7902,'SQL',date '1999-12-13',NULL); SQL> insert into registrations values (7006,'SQL',date '1999-12-13',NULL); SQL> insert into registrations values (7003,'JSON',date '1999-08-10',4 ); SQL> insert into registrations values (7012,'JSON',date '1999-08-10',4 ); SQL> insert into registrations values (7902,'JSON',date '1999-08-10',5 ); SQL> insert into registrations values (7010,'JSON',date '2000-09-27',5 ); SQL> insert into registrations values (7002,'JAVA',date '1999-12-13',2 ); SQL> insert into registrations values (7007,'JAVA',date '1999-12-13',NULL ); SQL> insert into registrations values (7011,'JAVA',date '1999-12-13',5 ); SQL> insert into registrations values (7008,'JAVA',date '1999-12-13',5 ); SQL> insert into registrations values (7009,'JAVA',date '1999-12-13',4 ); SQL> insert into registrations values (7004,'JAVA',date '2000-02-01',3 ); SQL> insert into registrations values (7008,'JAVA',date '2000-02-01',4 ); SQL> insert into registrations values (7006,'JAVA',date '2000-02-01',5 ); SQL> insert into registrations values (7012,'XML',date '2000-02-03',4 ); SQL> insert into registrations values (7002,'XML',date '2000-02-03',5 ); SQL> insert into registrations values (7004,'PLS',date '2000-09-11',NULL); SQL> insert into registrations values (7002,'PLS',date '2000-09-11',NULL); SQL> insert into registrations values (7011,'PLS',date '2000-09-11',NULL); SQL> SQL> SQL> select empno, ename, init 2 from emp 3 where empno in (7002,7004,7008); EMPNO | ENAME | INIT --------- | -------- | ----- 07002.00 | ALLEN | JAM 07004.00 | JACK | JM 07008.00 | SCOTT | DEF SQL> SQL> select * from registrations 2 where evaluation NOT IN (3,4,5); ATTENDEE | COURSE | BEGINDATE | EVALUATION --------- | ------ | --------- | ---------- 07011.00 | SQL | 12-APR-99 | 00002.00 07002.00 | JAVA | 13-DEC-99 | 00002.00 SQL>