Mengenoperatoren

Tabellen anlegen

CREATE TABLE schuhbar(
  ID INT PRIMARY KEY     NOT NULL
, NAME           TEXT    NOT NULL
, VNAME          TEXT    NOT NULL
);


CREATE TABLE outdoor(
  ID INT PRIMARY KEY     NOT NULL
, NAME           TEXT    NOT NULL
, VNAME          TEXT    NOT NULL
);

Datensätze einfügen

INSERT INTO schuhbar VALUES(1,'Miki','Maus');
INSERT INTO schuhbar VALUES(2,'Piti','Platsch');
INSERT INTO schuhbar VALUES(3,'Krümel','Monster');
INSERT INTO schuhbar VALUES(4,'Frosch','Kermit');
INSERT INTO schuhbar VALUES(5,E'Käpt\'n','Blaubär');

INSERT INTO outdoor VALUES(1,'Miki','Maus');
INSERT INTO outdoor VALUES(2,'Digedags','Dig');
INSERT INTO outdoor VALUES(3,'Digedags','Dag');
INSERT INTO outdoor VALUES(4,'Digedags','Digedag');
INSERT INTO outdoor VALUES(5,'Donald','Duck');

UNION

select * from schuhbar
union
select * from outdoor;

id |   name   |  vname
---+----------+---------
  5 | Käpt'n   | Blaubär
  2 | Digedags | Dig
  4 | Frosch   | Kermit
  3 | Krümel   | Monster
  1 | Miki     | Maus
  5 | Donald   | Duck
  4 | Digedags | Digedag
  2 | Piti     | Platsch
  3 | Digedags | Dag
(9 rows)

UNION ALL

select * from schuhbar
union all
select * from outdoor;

id |   name   |  vname
---+----------+---------
 1 | Miki     | Maus
 2 | Piti     | Platsch
 3 | Krümel   | Monster
 4 | Frosch   | Kermit
 5 | Käpt'n   | Blaubär
 1 | Miki     | Maus
 2 | Digedags | Dig
 3 | Digedags | Dag
 4 | Digedags | Digedag
 5 | Donald   | Duck
(10 rows)

EXCEPT

select * from schuhbar
except
select * from outdoor;

id |  name  |  vname
----+--------+---------
  5 | Käpt'n | Blaubär
  2 | Piti   | Platsch
  4 | Frosch | Kermit
  3 | Krümel | Monster
(4 rows)

INTERSECT

 select * from schuhbar
 intersect
 select * from outdoor;

 id | name | vname
----+------+-------
  1 | Miki | Maus
 (1 row)