Просмотр исходного текста страницы Oracle PL/SQL/Subquery/Exists Subquery

==Correlated subquery using the EXISTS operator==



   
  <!-- start source code -->
   
    <source lang="sql">
  
SQL>
SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.

SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.

SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.

SQL>
SQL> CREATE TABLE DEPT(
  2      DEPTNO NUMBER(2),
  3      DNAME VARCHAR2(14),
  4      LOC VARCHAR2(13)
  5  );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL> SELECT e1.ename, d.dname
  2      FROM   emp e1, dept d
  3      WHERE  e1.deptno = d.deptno
  4      AND EXISTS
  5      (SELECT "x" FROM emp e2
  6      WHERE  e2.ename = "Smart" AND    e2.deptno = e1.deptno)
  7  /
ENAME      DNAME
---------- --------------
Jane       RESEARCH
Smart      RESEARCH
Fake       RESEARCH
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
   
    
  </source>
    
   


==exists in subquery==



   
  <!-- start source code -->
   
    <source lang="sql">
  
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "TRAINER", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "TRAINER", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "TRAINER", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL> create table offerings
  2  ( course     VARCHAR2(6)
  3  , begindate  DATE
  4  , trainer    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> select e.*
  2  from   emp e
  3  where  exists (select o.*
  4                 from   offerings o
  5                 where  o.course = "SQL"
  6                 and    o.trainer = e.empno);
     EMPNO ENAME    INIT  JOB             MGR BDATE             SAL       COMM
---------- -------- ----- -------- ---------- ---------- ---------- ----------
    DEPTNO
----------
         1 Tom      N     TRAINER          13 17-12-1965        800
        20
        13 Fake     MG                      4 13-02-1959       3000
        20

SQL> drop table offerings;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
   
    
  </source>
    
   


==not exists and subquery==



   
  <!-- start source code -->
   
    <source lang="sql">
  
SQL>
SQL> create table offerings
  2  ( course     VARCHAR2(6)
  3  , begindate  DATE
  4  , trainer    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> 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>
SQL> select o.*
  2  from   offerings o
  3  where  not exists
  4        (select r.*
  5         from   registrations r
  6         where  r.course    = o.course
  7         and    r.begindate = o.begindate);
COURSE BEGINDATE     TRAINER LOCATION
------ ---------- ---------- --------
JAV    13-12-2009          4 SEATTLE
XML    03-02-2000          1 DALLAS
JAV    01-02-2000         11 DALLAS
PLS    11-09-2000          8 DALLAS
XML    18-09-2000            SEATTLE
OAU    27-09-2000         13 DALLAS
ERM    15-01-2001
PRO    19-02-2001            DALLAS
RSD    24-02-2001          8 CHICAGO
9 rows selected.
SQL> drop table offerings;
Table dropped.
SQL> drop table registrations;
Table dropped.
SQL>
   
    
  </source>
    
   


==not exists subquery==



   
  <!-- start source code -->
   
    <source lang="sql">
  
SQL>
SQL>
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> insert into gift(gift_id  ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
  2                 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created.
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> select emp_id, lastname
  2    from emp c
  3   where not exists
  4    (select * from gift o where o.emp_id = c.emp_id);
    EMP_ID LASTNAME
---------- --------------------
         3 X
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table gift;
Table dropped.
   
    
  </source>
    
   


==Using the EXISTS Operator with subquery==



   
  <!-- start source code -->
   
    <source lang="sql">
  
SQL> CREATE TABLE empExam (
  2     empID  INT NOT NULL,
  3     ExamID     INT NOT NULL,
  4     Mark       INT,
  5     Taken   SMALLINT,
  6     Comments   VARCHAR(255),
  7     CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));
Table created.
SQL>
SQL>
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,1,55,1,"Satisfactory");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,2,73,1,"Good result");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,3,44,1,"Hard");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,5,39,0,"Simple");
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken) VALUES (2,6,63,1);
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE emp (
  2     empID INT NOT NULL PRIMARY KEY,
  3     Name      VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor");
1 row created.
SQL>
SQL>
SQL> SELECT empID, Name FROM emp s
  2  WHERE EXISTS (
  3     SELECT empID FROM empExam e
  4     WHERE Mark < 40 AND e.empID = s.empID);
     EMPID NAME
---------- --------------------------------------------------
         2 Jack
1 row selected.
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table empExam;
Table dropped.
   
    
  </source>