Oracle PL/SQL Tutorial/Query Select/NOT IN

Материал из SQL эксперт
Перейти к: навигация, поиск

Deal with NULL with NOT IN and NVL

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    EMPNO         NUMBER(3),
 3    ENAME         VARCHAR2(15 BYTE),
 4    HIREDATE      DATE,
 5    ORIG_SALARY   NUMBER(6),
 6    CURR_SALARY   NUMBER(6),
 7    REGION        VARCHAR2(1 BYTE),
 8    MANAGER_ID    NUMBER(3)
 9  )
10  /

Table created. SQL> SQL> create table job (

 2    EMPNO         NUMBER(3),
 3    jobtitle      VARCHAR2(20 BYTE)
 4  )
 5  /

Table created. SQL> SQL> insert into job (EMPNO, Jobtitle) values (1,"Tester"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (2,"Accountant"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (3,"Developer"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (4,"COder"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (5,"Director"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (8,"Programmer"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer"); 1 row created. SQL> SQL> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (NULL,      "Jason", to_date("19960725","YYYYMMDD"), 1234,              8767,         "E",    2)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (2,      "John",  to_date("19970715","YYYYMMDD"), 2341,              3456,         "W",    3)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (3,      "Joe",   to_date("19860125","YYYYMMDD"), 4321,              5654,         "E",    3)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (4,      "Tom",   to_date("20060913","YYYYMMDD"), 2413,              6787,         "W",    4)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (5,      "Jane",  to_date("20050417","YYYYMMDD"), 7654,              4345,         "E",    4)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (6,      "James", to_date("20040718","YYYYMMDD"), 5679,              6546,         "W",    5)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (7,      "Jodd",  to_date("20030720","YYYYMMDD"), 5438,              7658,         "E",    6)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)

 2               values (8,      "Joke",  to_date("20020101","YYYYMMDD"), 8765,              4543,         "W")
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)

 2               values (9,      "Jack",  to_date("20010829","YYYYMMDD"), 7896,              1232,         "E")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /
    EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R MANAGER_ID

--------------- --------- ----------- ----------- - ----------
          Jason           25-JUL-96        1234        8767 E          2
        2 John            15-JUL-97        2341        3456 W          3
        3 Joe             25-JAN-86        4321        5654 E          3
        4 Tom             13-SEP-06        2413        6787 W          4
        5 Jane            17-APR-05        7654        4345 E          4
        6 James           18-JUL-04        5679        6546 W          5
        7 Jodd            20-JUL-03        5438        7658 E          6
        8 Joke            01-JAN-02        8765        4543 W
        9 Jack            29-AUG-01        7896        1232 E

9 rows selected. SQL> select * from job

 2  /
    EMPNO JOBTITLE

--------------------
        1 Tester
        2 Accountant
        3 Developer
        4 COder
        5 Director
        6 Mediator
        7 Proffessor
        8 Programmer
        9 Developer

9 rows selected. SQL> SQL> SELECT empno, ename

 2  FROM employee
 3  WHERE empno NOT IN
 4    (SELECT NVL(empno, 0)
 5     FROM employee);


SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> drop table job

 2  /

Table dropped. SQL> SQL></source>


Not equals and subquery

   <source lang="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> SQL> create table departments

 2  ( deptno NUMBER(2)     constraint D_PK
 3                         primary key
 4  , dname  VARCHAR2(10)
 5  , location VARCHAR2(8)
 6  , mgr    NUMBER(4)
 7  ) ;

Table created. SQL> SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7); 1 row created. SQL> insert into departments values (20,"TRAINING", "DALLAS", 4); 1 row created. SQL> insert into departments values (30,"SALES", "CHICAGO", 6); 1 row created. SQL> insert into departments values (40,"HR", "BOSTON", 9); 1 row created. SQL> SQL> select empno

 2  from   emp
 3  where  deptno <> (select deptno
 4                    from   departments
 5                    where  dname = "TRAINING");

Enter...

    2
    3
    5
    6
    7
    9
   10
   12
   14

9 rows selected. SQL> SQL> drop table departments; Table dropped. SQL> drop table emp; Table dropped.</source>


NOT IN and subquery

   <source lang="sql">

SQL> CREATE TABLE emps (

 2    emp varchar(30)
 3   ,title    varchar(30)
 4  );

Table created. SQL> SQL> INSERT INTO emps VALUES ("Tom","Programmer"); 1 row created. SQL> INSERT INTO emps VALUES ("Jack","Tester"); 1 row created. SQL> INSERT INTO emps VALUES ("Mary","Technician"); 1 row created. SQL> SQL> CREATE TABLE JobLevel (

 2    title     varchar(30)
 3   ,rank    varchar(30)
 4  );

Table created. SQL> SQL> INSERT INTO JobLevel VALUES ("Programmer","Level1"); 1 row created. SQL> INSERT INTO JobLevel VALUES ("Tester","Level2"); 1 row created. SQL> INSERT INTO JobLevel VALUES ("Technician","Level3"); 1 row created. SQL> SQL> CREATE TABLE salary (

 2    rank     varchar(30)
 3   ,payment  DECIMAL(10,2)
 4  );

Table created. SQL> SQL> INSERT INTO salary VALUES ("Level1",2000.00); 1 row created. SQL> INSERT INTO salary VALUES ("Level2",3000.00); 1 row created. SQL> INSERT INTO salary VALUES ("Level3",5000.00); 1 row created. SQL> INSERT INTO salary VALUES ("Level4",6000.00); 1 row created. SQL> SQL> select * from emps;

EMP


TITLE


Tom Programmer Jack Tester Mary Technician 3 rows selected. SQL> select * from JobLevel;

TITLE


RANK


Programmer Level1 Tester Level2 Technician Level3

3 rows selected. SQL> select * from salary; RANK PAYMENT


----------

Level1 2000 Level2 3000 Level3 5000 Level4 6000 4 rows selected. SQL> SQL> SELECT salary.rank FROM salary

 2  WHERE rank NOT IN (SELECT rank FROM JobLevel);

RANK


Level4 1 row selected. SQL> SQL> DROP TABLE emps; Table dropped. SQL> DROP TABLE JobLevel; Table dropped. SQL> DROP TABLE salary; Table dropped. SQL> SQL></source>


NOT IN returns false if a value in the list is null

   <source lang="sql">

SQL> 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 NOT IN (2, 3, NULL); no rows selected SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL></source>


NOT IN reverses the rows selected by IN

   <source lang="sql">

SQL> 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 NOT IN (2, 3, 5); 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 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 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 SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped.</source>


Subquery "NOT IN"

   <source lang="sql">

SQL> SQL> SQL> SELECT COUNT(*) num_owned, a.owner

 2      FROM dba_objects a
 3      WHERE a.owner NOT IN (SELECT b.owner FROM dba_objects b
 4      WHERE b.owner LIKE "SYS%")
 5      GROUP BY a.owner;
NUM_OWNED OWNER

------------------------------
      473 MDSYS
        3 TSMSYS
     1143 FLOWS_020100
     2769 PUBLIC
        8 OUTLN
      575 sqle
      339 CTXSYS
       34 HR
       12 FLOWS_FILES
       46 DBSNMP
      668 XDB

11 rows selected.</source>