SQL Set Operations

Contents

SQL Set Operations

In mathematics, a set is a collection of distinct entities.
On way to operate on sets of data is to use the set operators known from mathematics
It is often mo readable the joins and sub select

Operator INTERSECT

The intersection A ∩ B of two sets A and B is the set that contains all elements of A that also belong to B.
Find contracts that are are longer the 2 Years and have and iPhone: 
   select contract_id from contracts  where minimum_term >= 24 
   INTERSECT 
   select contract_id from device where device like 'iPhone%'

This can be also expressed by and equi-join or sub-select which is much less readable: 
   select contract_id from contracts  
   join device on  contracts.contract_id = device.contract_id 
   where device like 'iPhone%' and minimum_term >= 24

Operator UNION / UNION ALL

The union of two sets A ∪ B is the collection of entities which are in both A and B. Union all avoids sorting operation to remove duplicates. You can use it when you are sure that the sets are distinct.

You can use union all to create some in-place test data


   select contract_id from
   (
   select 31795  from dual union all
   select 36253 from dual union all
   select 38248 from dual union all
   select 39221 from dual union all
   select 51202 from dual 
   );

Operator MINUS


Contracts that are 24 month minimum term that have no open payments 
   select contract_id from contracts  where minimum_term >= 24 
   MINUS 
   select contract_id from payment where pending is not null; 


   create table aha_01
   (
      f1 varchar2(20),
      f2 varchar2(20)
   );


   create table aha_02
   (
      v1 varchar2(20),
      v2 varchar2(20)
   );



   insert into aha_01 values ('A1', '1');



   insert into aha_02 values ('A1', '1');


   commit


   select  * from aha_01;


   F1	F2
   A1	1


   select  * from aha_02;


   V1	V2
   A1	1
   A1	1



   select * from aha_01
   minus
   select * from aha_02
   null


   select * from aha_02
   minus
   select * from aha_01
   null


   insert into aha_02 values ('A1', '2');
   insert into aha_02 values ('C1', '1');
   insert into aha_01 values ('B1', '5');
   commit


   select  * from aha_01;
   F1	F2
   A1	1
   B1	4
   B1	5


   select  * from aha_02;
   V1	V2
   A1	1
   A1	1
   A1	2
   C1	1


   select * from aha_01
   minus
   select * from aha_02
   F1	F2
   B1	4
   B1	5


   select * from aha_02
   minus
   select * from aha_01
   V1	V2
   A1	2
   C1	1




Status: Published Date: 2017/06/05 14:14:14 Revision: 1.1
Copyright bei Andreas Haack (C) 2014. Diese Seite wird so wie sie ist zur Verfuegung gestellt, ohne irgenweche Garantien der Verwendbarkeit fuer bestimte Zwecke. Die auf dieser Seiten angebrachten Links liegen ausserhalb der redaktionellen Verantwortung von Andreas Haack und es wird keine Haftung oder Garantie uebernommen. Die Seiten sind Copyright (c) 2014 von Andreas Haack. Kein Teil darf ohne die schriftliche Einverstaendnis von Andreas Haack veroeffentlicht werden.
The page is provided 'as is' , without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fit- ness for a particular purpose and non-infringement. In no event shall Andreas Haack be liable for any claim, damages or other liability. This page is copyrighted property of Andreas Haack. Copyright by Andreas Haack (c) 2014 . No part of this page may be published without written permission for Andreas Haack. A hyper-link may created to this page but NOT to the embedded elements of this page. It may be freely downloaded for private purpose only as long as it is unaltered.