Oracle PL/SQL Tutorial/Query Select/IN
Содержание
- 1 Combine subquery and other conditions
- 2 Get all managers
- 3 In and subquery
- 4 In subquery
- 5 List all non-manager employee
- 6 Map pair with subquery by using IN operator
- 7 Subquery and update statement
- 8 Use in with subquery
- 9 Use null value with IN operator
- 10 Use varchar2 type value with IN operator
- 11 Using the IN Operator
- 12 Value pair and in operator
Combine subquery and other conditions
<source lang="sql">
SQL> SQL> SQL> create table course_schedule
2 ( course VARCHAR2(6) 3 , begindate DATE 4 , trainer NUMBER(4) 5 , location VARCHAR2(20)) ;
Table created. SQL> SQL> SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL ); 1 row created. SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO"); 1 row created. SQL> SQL> SQL> create table courses
2 ( code VARCHAR2(6) 3 , description VARCHAR2(30) 4 , category CHAR(3) 5 , duration NUMBER(2)) ;
SQL> SQL> SQL> insert into courses values("SQL","SQL course", "GEN",4); 1 row created. SQL> insert into courses values("OAU","Oracle course", "GEN",1); 1 row created. SQL> insert into courses values("JAV","Java course", "BLD",4); 1 row created. SQL> insert into courses values("PLS","PL/SQL course", "BLD",1); 1 row created. SQL> insert into courses values("XML","XML course", "BLD",2); 1 row created. SQL> insert into courses values("ERM","ERM course", "DSG",3); 1 row created. SQL> insert into courses values("PMT","UML course", "DSG",1); 1 row created. SQL> insert into courses values("RSD","C# course", "DSG",2); 1 row created. SQL> insert into courses values("PRO","C++ course", "DSG",5); 1 row created. SQL> insert into courses values("GEN","GWT course", "DSG",4); 1 row created. SQL> SQL> SQL> SQL> select *
2 from course_schedule 3 where begindate between date "1999-01-01" and date "1999-12-31" 4 and course in (select code from courses where category = "GEN");
COURSE BEGINDATE TRAINER LOCATION
--------- ---------- --------------------
SQL 13-DEC-99 4 DALLAS SQL 04-OCT-99 3 SEATTLE SQL 12-APR-99 1 VANCOUVER OAU 10-AUG-99 2 CHICAGO SQL> SQL> SQL> drop table course_schedule; Table dropped. SQL> SQL> drop table courses; Table dropped. SQL> SQL></source>
Get all managers
<source lang="sql">
SQL> SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> select empno, ename, init
2 from employees
3 where empno in (select mgr
4 from employees);
EMPNO ENAME INIT
-------- -----
2 Jerry J
3 Jord T
4 Mary J
5 Joe P
6 Black R
7 Red A
8 White S
9 Yellow C
10 Pink J
9 rows selected. SQL> SQL> drop table employees; Table dropped. SQL> SQL></source>
In and subquery
<source lang="sql">
SQL> create table courses
2 ( code VARCHAR2(6) constraint C_PK primary key 3 , description VARCHAR2(30) 4 , category CHAR(3) 5 , duration NUMBER(2) 6 ) ;
Table created. SQL> insert into courses values("SQL","SQL","GEN",4); 1 row created. SQL> insert into courses values("OAU","Java","GEN",1); 1 row created. SQL> insert into courses values("JAV","C++","BLD",4); 1 row created. SQL> insert into courses values("PLS","C","BLD",1); 1 row created. SQL> insert into courses values("XML","XML","BLD",2); 1 row created. SQL> insert into courses values("ERM","ERP","DSG",3); 1 row created. SQL> insert into courses values("PMT","ERP","DSG",1); 1 row created. SQL> insert into courses values("RSD","jQuery","DSG",2); 1 row created. SQL> insert into courses values("PRO","Linux","DSG",5); 1 row created. SQL> insert into courses values("GEN","Oracle","DSG",4); 1 row created. SQL> SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1) 6 , constraint R_PK primary key (attendee,course,begindate) 7 ) ;
Table created. SQL> SQL> insert into registrations values (2,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (14,"SQL",date "2009-04-12",5 ); 1 row created. SQL> insert into registrations values (6,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (11,"SQL",date "2009-04-12",2 ); 1 row created. SQL> insert into registrations values (8,"SQL",date "2009-10-04",NULL); 1 row created. SQL> insert into registrations values (9,"SQL",date "2009-10-04",3 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-10-04",4 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-12-13",NULL); 1 row created. SQL> insert into registrations values (6,"SQL",date "2009-12-13",NULL); 1 row created. SQL> insert into registrations values (3,"OAU",date "2009-08-10",4 ); 1 row created. SQL> insert into registrations values (12,"OAU",date "2009-08-10",4 ); 1 row created. SQL> SQL> select r.attendee, r.course, r.begindate
2 from registrations r 3 where r.course in (select c.code 4 from courses c 5 where c.category="BLD");
no rows selected SQL> drop table registrations; Table dropped. SQL> drop table courses; Table dropped. SQL></source>
In subquery
<source lang="sql">
SQL> SQL> create table emp(
2 emp_id integer primary key 3 ,lastname varchar2(20) not null 4 ,firstname varchar2(15) not null 5 ,midinit varchar2(1) 6 ,street varchar2(30) 7 ,city varchar2(20) 8 ,state varchar2(2) 9 ,zip varchar2(5) 10 ,shortZipCode varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50));
Table created. SQL> SQL> SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");
1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");
1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");
1 row created. SQL> SQL> create table gift(
2 gift_id integer primary key 3 ,emp_id integer 4 ,register_date date not null 5 ,total_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,message varchar2(100) 12 );
Table created. SQL> SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created. SQL> SQL> select firstname || " " || lastname as "Name", "(" || area_code || ")" || phone as "Telephone"
2 from emp 3 where emp_id in 4 (select emp_id from gift where register_date > add_months(sysdate, -1));
no rows selected SQL> SQL> drop table emp; Table dropped. SQL> drop table gift; Table dropped.</source>
List all non-manager employee
<source lang="sql">
SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> select empno, ename, init
2 from employees
3 where empno not in (select mgr
4 from employees
5 where mgr is not null);
EMPNO ENAME INIT
-------- -----
1 Jason N
SQL> SQL> SQL> drop table employees; Table dropped. SQL> SQL></source>
Map pair with subquery by using IN operator
<source lang="sql">
SQL> SQL> SQL> create table course_schedule
2 ( course VARCHAR2(6) 3 , begindate DATE 4 , trainer NUMBER(4) 5 , location VARCHAR2(20)) ;
Table created. SQL> SQL> SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL ); 1 row created. SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO"); 1 row created. SQL> SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1)) ;
Table created. SQL> SQL> SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 ); 1 row created. SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 ); 1 row created. SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 ); 1 row created. SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 ); 1 row created. SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 ); 1 row created. SQL> insert into registrations values (8, "JAV",date "2000-02-01",5 ); 1 row created. SQL> insert into registrations values (9, "XML",date "2000-02-03",4 ); 1 row created. SQL> insert into registrations values (10,"XML",date "2000-02-03",5 ); 1 row created. SQL> insert into registrations values (1, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (2, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (3, "PLS",date "2000-09-11",NULL); 1 row created. SQL> SQL> SQL> select attendee
2 from registrations 3 where (course, begindate) in 4 (select course, begindate 5 from course_schedule 6 where location = "CHICAGO"); ATTENDEE
4
SQL> SQL> SQL> drop table registrations; Table dropped. SQL> SQL> drop table course_schedule; Table dropped. SQL></source>
Subquery and update statement
<source lang="sql">
SQL> SQL> SQL> create table course_schedule
2 ( course VARCHAR2(6) 3 , begindate DATE 4 , trainer NUMBER(4) 5 , location VARCHAR2(20)) ;
Table created. SQL> SQL> SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL ); 1 row created. SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO"); 1 row created. SQL> SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1)) ;
Table created. SQL> SQL> SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 ); 1 row created. SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 ); 1 row created. SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 ); 1 row created. SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 ); 1 row created. SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 ); 1 row created. SQL> insert into registrations values (8, "JAV",date "2000-02-01",5 ); 1 row created. SQL> insert into registrations values (9, "XML",date "2000-02-03",4 ); 1 row created. SQL> insert into registrations values (10,"XML",date "2000-02-03",5 ); 1 row created. SQL> insert into registrations values (1, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (2, "PLS",date "2000-09-11",NULL); 1 row created. SQL> insert into registrations values (3, "PLS",date "2000-09-11",NULL); 1 row created. SQL> SQL> SQL> update registrations
2 set evaluation = 1 3 where (course,begindate) 4 in (select course,begindate 5 from course_schedule 6 where location = "CHICAGO");
1 row updated. SQL> SQL> drop table course_schedule; Table dropped. SQL> SQL> drop table registrations; Table dropped. SQL></source>
Use in with subquery
<source lang="sql">
SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> create table course_schedule
2 ( course VARCHAR2(6) 3 , begindate DATE 4 , trainer NUMBER(4) 5 , location VARCHAR2(20)) ;
Table created. SQL> SQL> SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL ); 1 row created. SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO"); 1 row created. SQL> SQL> SQL> select *
2 from employees
3 where empno in
4 (select id, begindate
5 from course_schedule
6 where location = "CHICAGO");
(select id, begindate
*
ERROR at line 4: ORA-00913: too many values
SQL> SQL> drop table employees; Table dropped. SQL> SQL> drop table course_schedule; Table dropped. SQL></source>
Use null value with IN operator
<source lang="sql">
SQL> SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1)) ;
Table created. SQL> SQL> SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 ); 1 row created. SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL); 1 row created. SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 ); 1 row created. SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 ); 1 row created. SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 ); 1 row created. SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 ); 1 row created. SQL> insert into registrations values (8, "JAV",date "2001-02-01",5 ); 1 row created. SQL> insert into registrations values (9, "XML",date "2002-02-03",4 ); 1 row created. SQL> insert into registrations values (10,"XML",date "2003-02-03",5 ); 1 row created. SQL> insert into registrations values (1, "PLS",date "2004-09-11",NULL); 1 row created. SQL> insert into registrations values (2, "PLS",date "2005-09-11",NULL); 1 row created. SQL> insert into registrations values (3, "PLS",date "2006-09-11",NULL); 1 row created. SQL> SQL> SQL> select *
2 from registrations 3 where evaluation not in (1,2,3,NULL);
no rows selected SQL> SQL> SQL> drop table registrations; Table dropped. SQL> SQL></source>
Use varchar2 type value with IN operator
<source lang="sql">
SQL> SQL> SQL> create table course_schedule
2 ( course VARCHAR2(6) 3 , begindate DATE 4 , trainer NUMBER(4) 5 , location VARCHAR2(20)) ;
Table created. SQL> SQL> SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE"); 1 row created. SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" ); 1 row created. SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL ); 1 row created. SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" ); 1 row created. SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO"); 1 row created. SQL> SQL> SQL> SQL> select distinct course
2 from course_schedule
3 where location in ("CHICAGO","SEATTLE");
COURSE
OAU JAV XML SQL RSD SQL> SQL> drop table course_schedule; Table dropped.</source>
Using the IN Operator
The IN operator in a WHERE clause selects only those rows whose column value is in a list.
The following SELECT statement uses the IN operator to retrieve rows from the employee table where the value in the id column is 2, 3, or 5:
<source lang="sql">
SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SELECT * FROM employee WHERE id IN (2, 3, 5); ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Value pair and in operator
<source lang="sql">
SQL> SQL> create table registrations
2 ( attendee NUMBER(4) 3 , course VARCHAR2(6) 4 , begindate DATE 5 , evaluation NUMBER(1) 6 , constraint R_PK primary key 7 (attendee,course,begindate) 8 ) ;
Table created. SQL> SQL> insert into registrations values (2,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (14,"SQL",date "2009-04-12",5 ); 1 row created. SQL> insert into registrations values (6,"SQL",date "2009-04-12",4 ); 1 row created. SQL> insert into registrations values (11,"SQL",date "2009-04-12",2 ); 1 row created. SQL> insert into registrations values (8,"SQL",date "2009-10-04",NULL); 1 row created. SQL> insert into registrations values (9,"SQL",date "2009-10-04",3 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-10-04",4 ); 1 row created. SQL> insert into registrations values (13,"SQL",date "2009-12-13",NULL); 1 row created. SQL> SQL> create table offerings
2 ( course VARCHAR2(6) 3 , begindate DATE 4 , Coder NUMBER(4) 5 , location VARCHAR2(8) 6 ) ;
Table created. SQL> insert into offerings values ("SQL",date "2009-04-12",13,"DALLAS" ); 1 row created. SQL> insert into offerings values ("OAU",date "2009-08-10",4,"CHICAGO"); 1 row created. SQL> insert into offerings values ("SQL",date "2009-10-04",1,"SEATTLE"); 1 row created. SQL> insert into offerings values ("SQL",date "2009-12-13",1,"DALLAS" ); 1 row created. SQL> insert into offerings values ("JAV",date "2009-12-13",4,"SEATTLE"); 1 row created. SQL> insert into offerings values ("XML",date "2000-02-03",1,"DALLAS" ); 1 row created. SQL> insert into offerings values ("JAV",date "2000-02-01",11,"DALLAS" ); 1 row created. SQL> insert into offerings values ("PLS",date "2000-09-11",8,"DALLAS" ); 1 row created. SQL> insert into offerings values ("XML",date "2000-09-18",NULL,"SEATTLE"); 1 row created. SQL> insert into offerings values ("OAU",date "2000-09-27",13,"DALLAS" ); 1 row created. SQL> insert into offerings values ("ERM",date "2001-01-15",NULL, NULL ); 1 row created. SQL> insert into offerings values ("PRO",date "2001-02-19",NULL,"DALLAS" ); 1 row created. SQL> insert into offerings values ("RSD",date "2001-02-24",8,"CHICAGO"); 1 row created. SQL> SQL> SQL> select attendee
2 from registrations 3 where (course, begindate) in 4 (select course, begindate 5 from offerings 6 where location = "CHICAGO");
no rows selected SQL> SQL> drop table offerings; Table dropped. SQL> drop table registrations; Table dropped. SQL></source>
