Test the NOT EXISTS version : EXISTS « Select Query « Oracle PL / SQL






Test the NOT EXISTS version

 
SQL> --Test the NOT EXISTS version
SQL>
SQL> create table customer(
  2           cust_no                   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          ,zip_4                             varchar2(4)
 11          ,area_code                         varchar2(3)
 12          ,phone                             varchar2(8)
 13          ,company_name                      varchar2(50)
 14  );

Table created.

SQL>
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(1, 'Allen', 'Joe','J','10 Ave','London','CA','11111','1111','111', '111-1111','Big Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(2,'Ward','Sue','W','20 Ave','New York','NY','44444','4444','444', '436-4444','B Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(3,'Jason','Pure','J','50 St','Longli','CA','55555','5555','555', '234-4444','C Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(4,'Bird','Jill', null,'30 St','Pais','NY','22222','2222','222', '634-7733','D Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(5,'Hill','Carl','H','19 Drive','A Town','CA','66666','6566','666', '243-4243','E Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(6,'Peter','Yari','P','38 Ave','Small City','NY','77777','7777','777', '454-5443','F Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(7,'Joe','Paula','J','78 St. Apt 3A','Queen City','NY','32322','2323','888', '664-4333','E Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(8,'Chili','Steve','C','38 Ave Apt 62','Mili','CA','88888','8888','787', '456-4566','G Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(9,'Mona','Joe','M','930 Ave933','Kansas City','MO','12345','1234','412', '456-4563','H Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(10,'Hack','Kisi','H','Kings Rd','Bellmore','NY','54321','3898','516', '767-5677','I Inc');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(11,'Bill','Jose','B','12 Giant Rd.','Newton','NJ','23454','1234','958', '123-7367','J Associates');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(12,'Taker','Lawrence','T','1 Sask Rd.','Camp','NJ','19191','3298','928', '123-7384','K Company');

1 row created.

SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(13,'Richer','Doris','R','213 Easy Street','WarPease','RI','34343','2112','501', '123-7384','L Inc');

1 row created.

SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
  2                values(14,'Pete','Doris','P','9 Ave','New York','NY','45454','4222','112', '123-1234','M Company');

1 row created.

SQL>
SQL> create table ord(
  2           order_no               integer          primary key
  3          ,cust_no                integer
  4          ,order_date             date not null
  5          ,total_order_price      number(7,2)
  6          ,deliver_date           date
  7          ,deliver_time           varchar2(7)
  8          ,payment_method         varchar2(2)
  9          ,emp_no                 number(3,0)
 10          ,deliver_name           varchar2(35)
 11          ,gift_message           varchar2(100)
 12  );

Table created.

SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
  2           values(1,1,'14-Feb-2002', 23.00, '14-Feb-2002', '12 noon', 'CA',1, null, 'Gift for wife');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(2,1,'14-Feb-2003', 510.98, '14-feb-2003', '5 pm', 'NY',7, 'Rose Ted', 'Happy Valentines Day to Mother');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(3, 2,'14-Feb-2004', 315.99, '14-feb-2004', '3 pm', 'VS',2, 'Ani Forest', 'Happy Valentines Day to Father');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(4, 2,'14-Feb-1999', 191.95, '14-feb-1999', '2 pm', 'NJ',2, 'O. John', 'Happy Valentines Day');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message    )
  2           values(5, 6,'4-mar-2002', 101.95, '5-mar-2002', '2:30 pm', 'MO'   , 2, 'Cora', 'Happy Birthday from John');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(6, 9,'7-apr-2003', 221.95, '7-apr-2003', '3 pm', 'MA', 2, 'Sake Keith', 'Happy Birthday from Joe' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(7, 9,'20-jun-2004', 315.95, '21-jun-2004', '12 noon', 'BC', 2, 'Jessica Li', 'Happy Birthday from Jessica');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (8, 12, '31-dec-1999', 135.95, '1-jan-2000', '12 noon', 'DI',      3, 'Larry', 'Happy New Year from Lawrence');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values (9, 12, '26-dec-2003', 715.95, '2-jan-2004', '12 noon', 'SK',7, 'Did', 'Happy Birthday from Nancy' );

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(10, 4, sysdate-1, 119.95, sysdate+2, '6:30 pm', 'VG',2, 'P. Jing', 'Happy Valentines Day to Jason');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
  2           values(11, 2, sysdate, 310.00, sysdate+2, '3:30 pm', 'DC',2, 'C. Late', 'Happy Birthday Day to Jack');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(12, 7, sysdate-3, 121.95, sysdate-2, '1:30 pm', 'AC',2, 'W. Last', 'Happy Birthday Day to You');

1 row created.

SQL> insert into ord(order_no  ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
  2           values(13, 7, sysdate, 211.95, sysdate-4, '4:30 pm', 'CA',2, 'J. Bond', 'Thanks for hard working');

1 row created.

SQL>
SQL>
SQL>
SQL> SELECT cust_no, lastname
  2    FROM customer c
  3    WHERE NOT EXISTS
  4    (SELECT cust_no FROM ord
  5     WHERE cust_no = c.cust_no);
   CUST_NO LASTNAME
---------- --------------------
         5 Hill
         8 Chili
         3 Jason
        10 Hack
        14 Pete
        13 Richer
        11 Bill

7 rows selected.

SQL>
SQL> drop table customer;

Table dropped.

SQL>
SQL> set autotrace off
SQL>
SQL> drop table ord;

Table dropped.

SQL>
SQL>
SQL> --

 








Related examples in the same category

1.Use EXISTS to link two queries
2.The EXISTS condition can also be combined with the NOT operator
3.Display the customer number and last name of all customers with no orders.