Use COUNT to count the rows from a subquery with set operator : Minus « Result Set « Oracle PL / SQL






Use COUNT to count the rows from a subquery with set operator

   
SQL> --
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 cust_history(
  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> insert into cust_history(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone)
  2                    values(3,'Brad','Ted','T','13 Street','Newton','RI','22222','3333','401', '123-4321');

1 row created.

SQL> insert into cust_history( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone)
  2                    values(4,'Wite','Doris','R','94 Ave','New York','NY','22299','3222','212', '123-1234');

1 row created.

SQL>
SQL>
SQL>
SQL> select count(*)
  2  from (select cust_no, lastname
  3         from customer
  4         minus
  5        select cust_no, lastname
  6         from cust_history );
  COUNT(*)
----------
        14

1 row selected.

SQL>
SQL> drop table customer;

Table dropped.

SQL> drop table cust_history;

Table dropped.

SQL>
SQL> --

   
    
  








Related examples in the same category

1.MINUS: returns all those records from the first query, but not the second
2.minus with like operator
3.One result set 'minus' another result set
4.An MINUS query that uses an ORDER BY clause
5.Minus id column
6.Understanding Intersections and Differences
7.All locations, where courses are offering, have no departments