PPT

Übung Datenbanksysteme
SQL-Anfragen (2)
18.12.2002
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Outer Joins
Motivation: Aufgabe 1a
• Betrachten Sie zusätzlich den Inhalt der beiden
Einzeltabellen
• Was passiert mit Fernzug H oder Wagen 36 ?
Erklärungen:
• Im Join tauchen nur die Tupel auf, die einen Joinpartner
haben
• Fernzug H hat keine Wagen
• Wagen 36 ist keinem Zug zugeordnet
• Ist das Verhalten wünschenswert ?
• Ja, aber:
• Was ist, wenn es wichtiger ist, alle Einträge zu sehen, auch
wenn es keinen „Partner gibt“
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Left outer Join
• Alle Elemente der “linken” Seite bleiben erhalten
• Elemente ohne Joinpartner werden mit NULL
aufgefüllt
• Syntax:
… from Tabelle1 t1
left (outer) join
Tabelle2 t2
on t1.spalte=t2.spalte
• “Linke” Tabelle ist t1
• Beispiel: Aufgabe 1b)
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Right outer Join
• Alle Elemente der rechten Seite bleiben erhalten
• Elemente ohne Joinpartner werden mit NULL
aufgefüllt
• Syntax:
… from Tabelle1 t1
right (outer) join
Tabelle2 t2
on t1.spalte=t2.spalte
• “Rechte” Tabelle ist t2
• Beispiel: Aufgabe 1c)
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Full Outer Join
Beide Seiten bleiben vollständig erhalten,
Joinpartner werden kombiniert
Elemente ohne Joinpartner werden mit NULL
aufgefüllt
• Syntax:
… from Tabelle1 t1
full outer join
Tabelle2 t2
on t1.spalte=t2.spalte
Beispiel: 1d)
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Outer Joins
• Wie könnte man Outer Joins mit den „klassischen“
Statements darstellen ?
• Q1: Inner Join, um die kombinierten Tupel zu
erzeugen
• Verbleibende Tupel finden und kombinieren ?
• Q2: Projektion von Q1 auf die Attribute von t1
(Semijoin)
• Q3: Mengendifferenz t1-Q2
• Q4: Aufweitung von Q3 auf alle Spalten (wie ?)
• Union von Q1 und Q4
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Subqueries
Konzept:
Bestimmte Elemente einer SQL-Query sind selbst wieder Queries
Welche ?
• Spalten im Select
• Tabellen im From
• Input von arithmetischen Operationen (Vorsicht !)
• Input von Mengenoperationen
• Input von Exists
Warum ?
• Mehr Ausdrucksmöglichkeiten
• Zusammenfassung mehrerer Teilabfragen zu einer einzelnen
• Strukturierung
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Subqueries im Select
• Hinzufügen von weiteren Spalten aus weiteren
Queries
• Syntax:
• Select a, b,
(select x from z)
From c
Siehe Aufgabe 2a)
Auf die Variablen der äußeren Anfrage kann von der
inneren zugegriffen werden
Modifizieren des Anfrage von 2a, um die
Rabattdifferenz anzuzeigen !
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Suqueries in arithmethischen Operationen
• Ergebnis der Subquery wird in
arithmetischen Ausdruck weiterverwendet
• Beispiel: Aufgabe 2b)
• Vorsicht:
• Ergebnis muss ein einzelner Wert sein !
• Nützlich für:
• Arithmetische Mengenvergleiche ?
• ...
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Subqueries bei FROM
•
•
•
•
„Zwischentabellen“ in einer Query
Syntax:
... FROM (Select * from a) <Name> ...
Name gibt der temporären Tabelle einen Namen – warum
notwendig ?
Vorteile:
• Integration mehrerer Teilqueries in eine einzelne Query
• Zwischenergebnisse müssen nicht explizit abgespeichert
werden
• Optimierer kann evtl. besseren Plan finden
• Beispiel: Aufgabe 2c)
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Subqueries mit Mengenoperatoren
• Ergebnis der Subquery stellt Menge dar, auf die
bestimmte Operation angewandt wird
• Wie muss das Ergebnis der Subquery aussehen ?
=> Schemata strukturell identisch !
Mengenoperatoren:
–
–
–
–
In
Union
intersect
Except
• Beispiel: Aufgabe 2d
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Subqueries mit Quantifikatioren
• Eigentlich Spezialfall der Mengenoperatoren
• Weniger häufig benötigt als im Tupelkalkül
• Wichtigster Operator:
EXISTS (bzw. NOT EXISTS)
• Explizites FOR ALL (noch) nicht Teil von SQL
• mit EXISTS ausdrücken
• Aber
• ALL oder SOME im Mengenvergleich
• > ALL ()
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Beispiel: Aufgabe 2e
• Finden Sie einen Zug, der zu allen Stationen
ausser Ingolstadt faehrt
• Lösungsvorschläge ???
• Allquantor worauf anwenden, wodurch
ersetzen ?
• Zählen ?
• Mengenvergleiche ???
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Aufgabe 2e: Lösungsansatz
Allquantifizierung ersetzen
• Ein Zug muss in allen Stationen (ohne Ingolstadt)
ankommen
• für alle Stationen (außer Ingolstadt) muss es eine
Verbindung geben, die zu dieser Station fährt und
bei der der Zug der gleiche ist
• kein „für alle“ in Standard-SQL
• Transformation von „für alle existiert ein“ zu
“es gibt keines, für das nicht existiert
• Probieren wir jetzt aus ...
Übung Datenbanksysteme WS 2002/2003
08.04.2017
Hinweise zur Verwendung von Subqueries
• Durch Subqueries gibt es viele semantisch äquivalente
Möglichkeiten, eine Anfrage auszudrücken !
• Vorsicht vor zu vielen Subqueries
• Warum nicht ?
• Viele Optimizer arbeiten schlecht mit Subqueries (Oracle)
• Vorsicht bei korrelierten Subqueries !
• Bei jedem korreliertem Wert der äußeren Query muss die
innere erneut ausgewertet werden
• Kann teuer werden
• Wenn möglich, Anfragen dekorrelieren !
Übung Datenbanksysteme WS 2002/2003
08.04.2017