Mengenoperationen in SQL

Mengenoperationen in SQL
• Mengenoperationen
- (Ungefähre) Entsprechungen der Mengenoperationen Vereinigung (∪), Differenz (–) und Schnittmenge (∩) der relationalen Algebra
- Erlauben das Zusammenführen der Ergebnis-Relationen R1 und R2 zweier
Queries Q1 und Q2 zu einer Ergebnisrelation T
- Erfordern Vereinigungsverträglichkeit der Eingabe-Relationen:
-Zuordnung jedoch über Spaltenposition statt über Spaltenname
-Typverträglichkeit der zusammengeführten Spalten erforderlich
⇒Evtl. Umordnung/Projektion der Spalten in der SELECT-Klausel der Eingabe-Anfragen erforderlich
- Jeweils in einer Version mit und ohne Duplikatelimierung verfügbar:
-Bei Angabe von DISTINCT: mit Duplikateliminierung
-Bei Angabe von ALL: keine Duplikateliminierung
-Standardverhalten (ohne Angabe von DISTINCT bzw. ALL):
mit Duplikateliminierung!
• Erweiterung des Syntaxdiagrams von Seite 4-4
full-query:
UNION
query
EXCEPT
DISTINCT
ALL
full-query
INTERSECT
- Einsetzen von full-query an Stelle von query in den anderen Teilen der
Grammatik
• Beispiel-Relationen
R1
A
B
INTEGER VARCHAR(10)
C
INTEGER
R2
A
X
Y
INTEGER
INTEGER
VARCHAR(10)
1
abc
42
4
16
hij
2
def
23
2
23
def
1
3
abc
42
42
abc
stu
15
1
3
15
vwx
• Vereinigung
- R1 UNION [DISTINCT] R2:
Vereinigt die Tupel der Eingabe-Relationen R1 und R2. Bestehen Duplikate in
R1 oder R2 oder entstünden Duplikate durch die Vereinigung, enthält das Ergebnis keine Duplikate.
- R1 UNION ALL R2:
Vereinigt die Tupel der Eingabe-Relationen R1 und R2, bestehende oder durch
die Vereinigung neu entstehende Duplikate bleiben vollständig erhalten
- Beispiele:
-SELECT * FROM R1 UNION DISTINCT SELECT * FROM R2
⇒Fehler, da R1.B nicht verträglich mit R1.X (bzw. R1.C n. vertr. mit R2.Y)
-SELECT * FROM R1 UNION DISTINCT SELECT A, Y, X FROM R2
⇒Vereinigungsverträglichkeit durch Umsortieren hergestellt
A
B
INTEGER VARCHAR(10)
C
INTEGER
1
abc
42
2
def
23
3
stu
15
4
3
hij
vwx
16
15
keine Duplikate!
-SELECT * FROM R1 UNION ALL SELECT A, Y, X FROM R2
A
B
INTEGER VARCHAR(10)
C
INTEGER
1
abc
42
2
def
23
1
3
abc
42
4
stu
hij
15
16
2
def
23
1
3
abc
42
vwx
15
Alle Duplikate aus R1
und R2 bleiben erhalten
• Differenz
- R1 EXCEPT [DISTINCT] R2:
Das Ergebnis enthält alle Tupel aus R1, die nicht auch in R2 enthalten sind.
Enthält R1 Duplikate eines Tupels r, das nicht ebenfalls (mindestens einmal) in
R2 existiert, so enthält das Ergebnis das Tupel r genau einmal.
- R1 EXCEPT ALL R2:
Das Ergebnis enthält alle Tupel aus R1, die nicht auch in R2 enthalten sind. Sei
n die Anzahl der Duplikate eines Tupels r aus R1, sei m die Anzahl der Duplikate des gleichen Tupels in R2, so ist die Anzahl der Duplikate im Ergebnis
MAX(m-n, 0)
- Beispiele
-SELECT * FROM R1 EXCEPT DISTINCT SELECT A, Y, X FROM R2
A
B
INTEGER VARCHAR(10)
3
stu
C
INTEGER
15
-SELECT * FROM R1 EXCEPT ALL SELECT A, Y, X FROM R2
A
B
INTEGER VARCHAR(10)
C
INTEGER
1
abc
42
3
stu
15
Tupel bleibt 1x im
Ergebnis erhalten (da 2x in R1
aber nur 1x in R2)
• Schnittmenge
- R1 INTERSECT [DISTINCT] R2:
Das Ergebnis enthält alle Tupel, die sowohl in R1 als auch in R2 enthalten sind.
Enthalten R1 und R2 Duplikate eines Tupels r, so enthält das Ergebnis das Tupel r genau einmal.
- R1 INTERSECT ALL R2:
Das Ergebnis enthält alle Tupel, die sowohl in R1als auch in R2 enthalten sind.
Sei n die Anzahl der Duplikate eines Tupels r aus R1, sei m die Anzahl der Duplikate des gleichen Tupels in R2, so ist die Anzahl der Duplikate im Ergebnis
MIN(m,n).
- Beispiele
-SELECT * FROM R1 INTERSECT DISTINCT SELECT A, Y, X FROM R2
A
B
C
INTEGER VARCHAR(10)
INTEGER
1
abc
42
2
def
23
-Zusätzliche Eingaberelation R2’:
R2’
A
X
Y
INTEGER
INTEGER
VARCHAR(10)
4
16
hij
2
23
def
1
3
42
abc
1
15
42
vwx
abc
2
23
def
Dann liefert
SELECT * FROM R1 INTERSECT ALL SELECT A, Y, X FROM R2’
R1
A
B
INTEGER VARCHAR(10)
C
INTEGER
1
abc
42
1
abc
42
2
def
23
2x in R1 und 2x in R2’
1x in R1 und 2x in R2’