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)