Unterschied zweier Abfragen

[stm]

Hallo,
Ich habe zwei Abfragen die nach meinen Überlegungen eigentlich äquivalent sein sollten.
Da sie das nicht sind würde ich gerne wissen, warum das so ist.

Abfrage 1:
[highlight=sql]
SELECT name,
anschrift
FROM MITARBEITERIN m
WHERE exists
( SELECT sum(Preis*STUECK)
FROM KELLNERIN k
JOIN TISCH t ON t.ZUSTAENDIG=k.pnr
LEFT OUTER JOIN Bestellung bes ON t.RESTNR=bes.RESTNR
AND t.STANDORT=bes.STANDORT
AND t.TISCHNR=bes.TISCHNR
LEFT OUTER JOIN Beinhaltet b ON b.BESTNR=bes.BESTNR
LEFT OUTER JOIN Gericht g ON g.id=b.id
WHERE k.pnr=m.pnr HAVING sum(Preis*STUECK)=
( SELECT min(Umsatz1)
FROM
(SELECT sum(g1.Preis*b1.STUECK) AS Umsatz1
FROM KELLNERIN k1
JOIN TISCH t1 ON t1.ZUSTAENDIG=k1.pnr
LEFT OUTER JOIN Bestellung bes1 ON t1.RESTNR=bes1.RESTNR
AND t1.STANDORT=bes1.STANDORT
AND t1.TISCHNR=bes1.TISCHNR
LEFT OUTER JOIN Beinhaltet b1 ON b1.BESTNR=bes1.BESTNR
LEFT OUTER JOIN Gericht g1 ON g1.id=b1.id
GROUP BY k1.pnr ) ))

[/highlight]
Abfrage 2:
[highlight=sql]
SELECT name,
anschrift
FROM MITARBEITERIN m
JOIN KELLNERIN k ON k.pnr=m.pnr
JOIN TISCH t ON t.ZUSTAENDIG=k.pnr
LEFT OUTER JOIN Bestellung bes ON t.RESTNR=bes.RESTNR
AND t.STANDORT=bes.STANDORT
AND t.TISCHNR=bes.TISCHNR
LEFT OUTER JOIN Beinhaltet b ON b.BESTNR=bes.BESTNR
LEFT OUTER JOIN Gericht g ON g.id=b.id
GROUP BY m.pnr,
anschrift,
name HAVING sum(Preis*STUECK)=
( SELECT min(Umsatz1)
FROM
(SELECT sum(g1.Preis*b1.STUECK) AS Umsatz1
FROM KELLNERIN k1
JOIN TISCH t1 ON t1.ZUSTAENDIG=k1.pnr
LEFT OUTER JOIN Bestellung bes1 ON t1.RESTNR=bes1.RESTNR
AND t1.STANDORT=bes1.STANDORT
AND t1.TISCHNR=bes1.TISCHNR
LEFT OUTER JOIN Beinhaltet b1 ON b1.BESTNR=bes1.BESTNR
LEFT OUTER JOIN Gericht g1 ON g1.id=b1.id
GROUP BY k1.pnr ) )
[/highlight]
Die Zwei Abfragen sollten die Kellner mit dem minimalen Umsatz liefern.
Bei der Ersten funktioniert das, bei der zweiten bekomme ich eine leere Relation zurück.
Das was sich zwischen den Abfragen verändert hat ist, das es statt dem exist einen inner join gibt in dessen on die vorherige where-Bedingung steht. Außerdem wurde eine entsprechendes Group by eingefügt.

BEINHALTET : (BESTNR, ID, STUECK)
BESTELLUNG: (BESTNR, TISCHNR, RESTNR, STANDORT)
GERICHT: (ID, PREIS…)
KELLNERIN: (PNR)
MITARBEITERIN : (PNR, NAME, ANSCHRIFT….)
TISCH: (RESTNR, STANDORT, TISCHNR, ZUSTAENDIG) // ZUSTAENDIG= KELLNERIN.Pnr

Schon mal danke für eure Mühe

[Wernfried]

Ein Exists liefert TRUE auch wenn das Resultat NULL ist, d.h. diese beiden Ausdrücke verhalten sich genau gleich:

[HIGHLIGHT=sql]SELECT name, anschrift
FROM MITARBEITERIN m
WHERE EXISTS
( SELECT SUM(Preis*STUECK)
FROM KELLNERIN k[/HIGHLIGHT][HIGHLIGHT=sql]SELECT name, anschrift
FROM MITARBEITERIN m
WHERE EXISTS
( SELECT NULL
FROM KELLNERIN k
[/HIGHLIGHT]

Ein Inner Join (oder IN) wertet ein NULL Resultat nicht aus.

Gruss

[stm]

Ein Inner Join (oder IN) wertet ein NULL Resultat nicht aus.

Das war mir nicht bewusst, aber ich sehe nicht ganz wie das den Unterschied der zwei Abfragen erklärt, es haben ja beide Abfragen ein Inner Join.

[Ralf Jansen]

Ein Exists liefert TRUE auch wenn das Resultat NULL ist

Der SubSelect im Exists liefert aber nicht zwingend etwas. Der Sum sollte immer zumindest null liefern aber nicht wenn die Having Clause die wieder wegfiltert. Having kommt ja nach dem Sum.


Zur Lösung des Problems - Das Konstrukt ist viel zu komplex um das als außenstehender zu durchschauen. Vereinfache das Ding bis es sich gleich verhält. Entweder erkennst du dann an dem was du weggenommen hast woran es liegt oder du kannst mit einer deutlich weniger komplexen Abfrage hier nochmal nachhaken.

[stm]

Abfrage 1 reduziert:
[highlight=sql]
SELECT name
FROM MITARBEITERIN m
WHERE EXISTS
(SELECT SUM(Preis*STUECK)
FROM KELLNERIN k
JOIN TISCH t ON t.ZUSTAENDIG=k.pnr
LEFT OUTER JOIN Bestellung bes ON t.RESTNR=bes.RESTNR
AND t.STANDORT=bes.STANDORT
AND t.TISCHNR=bes.TISCHNR
LEFT OUTER JOIN Beinhaltet b ON b.BESTNR=bes.BESTNR
LEFT OUTER JOIN Gericht g ON g.id=b.id
WHERE k.pnr=m.pnr
GROUP BY k.pnr HAVING SUM(Preis*STUECK)=24.7)
[/highlight]

Abfrage 2 reduziert:
[highlight=sql]
SELECT name
FROM MITARBEITERIN m
JOIN KELLNERIN k ON k.pnr=m.pnr
JOIN TISCH t ON t.ZUSTAENDIG=k.pnr
LEFT OUTER JOIN Bestellung bes ON t.RESTNR=bes.RESTNR
AND t.STANDORT=bes.STANDORT
AND t.TISCHNR=bes.TISCHNR
LEFT OUTER JOIN Beinhaltet b ON b.BESTNR=bes.BESTNR
LEFT OUTER JOIN Gericht g ON g.id=b.id
GROUP BY k.pnr,
name HAVING SUM(Preis*STUECK)=24.7
[/highlight]

24.7 ist der aktuell kleinste Umsatz ohne Nullwerte(wird vom subselect zurückgegeben) .
Die zwei Abfragen liefern die gleiche Ergebnisse wie die oberen.

Kann es vielleicht am group by liegen habe ich nämlich
[highlight=sql]
Select m.pnr
From MITARBEITERIN m join KELLNERIN ……
Groupb by m.pnr …..
[/highlight]
Kommt die richtige Pnr zurück, kann aber auch daran liegen das der Optimierer den inner join ganz weglässt(MITARBEITERIN wird ja dann eigentlich nicht benötigt).

[Wernfried]

Kommt die richtige Pnr zurück, kann aber auch daran liegen das der Optimierer den inner join ganz weglässt(MITARBEITERIN wird ja dann eigentlich nicht benötigt).

Ganz bestimmt nicht! Wenn es genau wissen willst, schau dir den Execution Plan an.

Was mich an deiner Abfrage am meisten wundert sind die OUTER JOINS, d.h. es kann Kellnerin geben die gar keine Bestellung entgegen genommen haben, sprich gar keinen Umsatz gemacht haben.
Wenn du die Kellnerin mit dem kleinsten Umsatz haben möchtst kannst du überall auch einen INNER JOIN machen, da dich die "umsatzlosen" nicht interessieren.

Sind die Resultate dann gleich?

Gruss

[stm]

Was mich an deiner Abfrage am meisten wundert sind die OUTER JOINS, d.h. es kann Kellnerin geben die gar keine Bestellung entgegen genommen haben, sprich gar keinen Umsatz gemacht haben.
Wenn du die Kellnerin mit dem kleinsten Umsatz haben möchtst kannst du überall auch einen INNER JOIN machen, da dich die "umsatzlosen" nicht interessieren.

Sind die Resultate dann gleich?
Gruss

Stimmt das ist was historisches von einer anderen Query, wo ich den Umsatz schon gebraucht habe.
Die Resultate sind dann bei allen 2, die gleichen wie bisher.

[Wernfried]

Was kommt denn raus, wenn du dieses hier machst?

[HIGHLIGHT=sql]
SELECT k.pnr, name, SUM(Preis*STUECK)
FROM MITARBEITERIN m
JOIN KELLNERIN k ON k.pnr=m.pnr
JOIN TISCH t ON t.ZUSTAENDIG=k.pnr
LEFT OUTER JOIN Bestellung bes ON t.RESTNR=bes.RESTNR
AND t.STANDORT=bes.STANDORT
AND t.TISCHNR=bes.TISCHNR
LEFT OUTER JOIN Beinhaltet b ON b.BESTNR=bes.BESTNR
LEFT OUTER JOIN Gericht g ON g.id=b.id
GROUP BY k.pnr,name
Order by 3
[/HIGHLIGHT]

... das was du erwarten würdest?
Der 1. Datensatz sollte das gewünschte Ergebnis sein.

Gruss

Gruss

[stm]

Was kommt denn raus, wenn du dieses hier machst?

[HIGHLIGHT=sql]
SELECT k.pnr, name, SUM(Preis*STUECK)
FROM MITARBEITERIN m
JOIN KELLNERIN k ON k.pnr=m.pnr
JOIN TISCH t ON t.ZUSTAENDIG=k.pnr
LEFT OUTER JOIN Bestellung bes ON t.RESTNR=bes.RESTNR
AND t.STANDORT=bes.STANDORT
AND t.TISCHNR=bes.TISCHNR
LEFT OUTER JOIN Beinhaltet b ON b.BESTNR=bes.BESTNR
LEFT OUTER JOIN Gericht g ON g.id=b.id
GROUP BY k.pnr,name
Order by 3
[/HIGHLIGHT]

... das was du erwarten würdest?

Verhält sich wie ich es erwarten würde nach einer kleinen Änderung.
Hab das einfachste übersehen,da ich die nicht benötigten Felder zu sehr ausgeklammert habe.
In Gericht gibt es auch einen Namen .
Danke für die ganze Hilfe, aber meldet sich hier SQL normal nicht, da es nicht eindeutig ist welches Feld man meint?

[Ralf Jansen]

Danke für die ganze Hilfe, aber meldet sich hier SQL normal nicht, da es nicht eindeutig ist welches Feld man meint?

SQL nicht aber das konkrete Datenbankmodel das das SQL interpretiert sollte das machen Und jede mir bekannte DB macht das eigentlich auch. Wo wir bei des Pudels Kern sind. Was ist das für eine DB?

[stm]

Ist ein Onlineübungssystem aber wegen den Fehlermeldungen wie ORA-00900: invalid SQL statement hätte ich auf ein Oracle-system getippt.

[Wernfried]

Ich glaube das Problem hatte ich auch schon mal. Wenn du Lust hast, schreibe die Abfrage mal in die Oracle Syntax um (mit INNER JOIN, das ist einfacher), ungefähr so:

[highlight=sql]
SELECT k.pnr, name, SUM(Preis*STUECK)
FROM MITARBEITERIN m, KELLNERIN k, TISCH t , Bestellung bes, Beinhaltet b, Gericht g
WHERE k.pnr=m.pnr
AND t.ZUSTAENDIG=k.pnr
AND t.RESTNR=bes.RESTNR
AND t.STANDORT=bes.STANDORT
AND t.TISCHNR=bes.TISCHNR
AND b.BESTNR=bes.BESTNR
AND g.id=b.id
...
[/highlight]

Ich meine seit Oracle 11.1 ist der Fehler behoben, kann mich aber täuschen.

Gruss

[defo]

Ich glaube das Problem hatte ich auch schon mal. Wenn du Lust hast, schreibe die Abfrage mal in die Oracle Syntax um (mit INNER JOIN, das ist einfacher), ungefähr so:
Ich meine seit Oracle 11.1 ist der Fehler behoben, kann mich aber täuschen.

Interessant!

Für die Akten:
- "name" ist ein reserved word in oracle
Es empfiehlt sich, in jedem System, ob DB oder was auch immer, solche Bezeichner nicht zu verwenden. Man kann davon ausgehen, dass hier extra Code angeflanscht wurde, damit es "seamless" verwendet werden kann.
unter oracle abzufragen mit > [highlight=SQL]select * from v$reserved_words[/highlight]

- immer Table Alias
bei Spaltennennung verwenden, spätestens, wenn die Abfrage nicht funktioniert/das gewünschte Ergebnis liefert

- Die Prüfung / Unterstützung anhand von Select Fragmenten
bei solchen Fehlern ist immer dann besonders mühselig, wenn weder Datenmodell noch Datenbank bekannt ist.
Gerade "Group by" Abfrage Ergebnisse lassen sich ohne Kenntnis von Primärschlüsseln, Unique Constraints u.U. nur schwer voraussagen.

- probehalber
aus einem Select Statement einen View erzeugen, hier gelten zumindest bei Oracle strengere Kriterien

- ANSI Join unter Oracle:
Oracle ist nicht der Erfinder der relationalen Datenbank, aber sie haben garantiert nicht den ANSI Join erfunden. Ich bin kein Freund von Gerüchten und Legenden, aber ich sage an der Stelle einfach mal, dass man bei Oracle mit der "where" clause vermutlich besser fährt. Es gibt nur sehr wenig (1?) Konstellationen, die man unter Oracle nur mit ANSI JOIN ausführen kann. Keine Frage, das sollte alles 100 pro funktionieren, die alte Syntax ist aber sicherlich um Größenordnungen besser erprobt und gehärtet. Ansonsten ist es m.E. Geschmackssache / Gewohnheit, welche Variante man nimmt.

P.S.: Mit einem einfachen Left outer group by Join bekomme ich eine Fehlermeldung unter Oracle 10 bei Verwendung eines nicht eindeutigen Feldnamens.

[Wernfried]

Für die Akten:
- "name" ist ein reserved word in oracle
Es empfiehlt sich, in jedem System, ob DB oder was auch immer, solche Bezeichner nicht zu verwenden. Man kann davon ausgehen, dass hier extra Code angeflanscht wurde, damit es "seamless" verwendet werden kann.
unter oracle abzufragen mit > [highlight=SQL]select * from v$reserved_words[/highlight]

Wenn ich mir die View ansehe, spricht meines Erachtens nichts gegen die Verwendung von "NAME" als Spaltennamen, da steht alles auf "N":
[HIGHLIGHT=sql]
SELECT * FROM v$reserved_words where keyword = 'NAME';

KEYWORD LENGTH RESERVED RES_TYPE RES_ATTR RES_SEMI DUPLICATE
------------ ---------- -------- -------- -------- -------- ---------
NAME 4 N N N N N

1 row selected.
[/HIGHLIGHT]

- ANSI Join unter Oracle:
Oracle ist nicht der Erfinder der relationalen Datenbank, aber sie haben garantiert nicht den ANSI Join erfunden. Ich bin kein Freund von Gerüchten und Legenden, aber ich sage an der Stelle einfach mal, dass man bei Oracle mit der "where" clause vermutlich besser fährt. Es gibt nur sehr wenig (1?) Konstellationen, die man unter Oracle nur mit ANSI JOIN ausführen kann. Keine Frage, das sollte alles 100 pro funktionieren, die alte Syntax ist aber sicherlich um Größenordnungen besser erprobt und gehärtet. Ansonsten ist es m.E. Geschmackssache / Gewohnheit, welche Variante man nimmt.

Oracle selber empfiehlt die Verwendung der ANSI Syntax, zumindest für OUTER JOINS:
"Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator."
Oracle listet 8 Punkte/Gründe für die ANSI Join.
http://docs.oracle.com/cd/B28359_01/...htm#SQLRF52337

Interessanterweise sind die meisten Beispiel in der Oracle Doc nach wie vor in Oracle Join Syntax geschrieben.

Ich selber hatte, soweit ich mich erinnere, drei oder vier Oracle SR's bei denen die (vorübergehende) Lösung hiess: Verwende Oracle Join Syntax anstatt ANSI Syntax - dann geht es.
Inzwischen sind die Bugs aber alle behoben und meiner Meinung nach gibt es - ausser der persönlichen Vorlieben - keinen Grund mehr die ANSI Join Syntax nicht zu verwenden.

Gruss

[Ralf Jansen]

Verwende Oracle Join Syntax anstatt ANSI Syntax - dann geht es.

Aus Neugier. Ist Oracle da tatsächlich so vermessen es Oracle Join zu nennen. Für mich sind beides ANSI Joins nur halt bezogen auf andere Versionen des Standards(SQL-86 vs. SQL-92)