Eltern-Kind-Abfrage mit SQL

[PatkIllA]

Ich habe eine Elterntabelle projects mit den Spalten id, name, usw. und eine Kind-Tabelle divisions mit den Spalten id, name, project_id.

Jetzt suche ich alle Einträge aus projects, die kein Kind mit name = 'a' haben. Also sowohl die ganz ohne Kinder oder nur Kinder, mit Name b,c usw.

Es geht mit einer Unterabfrage:
SELECT id, name FROM projects WHERE id NOT IN (SELECT project_id FROM divisions where name = 'a')

Ist das clever und geht das auch mit einem JOIN?

Datenbanksystem kann Microsoft SQL Server, PostgreSQL, SQLite oder Oracle sein.

[Rooter]

Was heißt "clever"? Funktionieren tut es. Ich hätte es auch so gemacht.
Oder geht es dir um die Performance?

MfG
Rooter

[PatkIllA]

Ich war halt nur erst dabei den vorhandenen Positivfall mit JOIN anzupassen.
Probleme mit Millionen Einträgen sollte es auch nicht geben oder?

Muss ich mal unsere Abstraktionsebene erweitern. Die kann sowas noch nicht...

[Matrix316]

Vielleicht so:

select a.* from projects a inner join divisions b on a.id = b.project_id where b.name <> 'a'

[PatkIllA]

Vielleicht so:

select a.* from projects a inner join divisions b on a.id = b.project_id where b.name <> 'a'
Da fehlen durch den inner join schon die projects ohne Kinder und findet dafür alle die mindestens ein Kind mit was anderem als a haben

[ManuelCalavera]

Evtl geht das?

select a.* from projects a
left join divisions b on a.id = b.project_id and b.name = 'a'
where b.id is null

[Ben Carter]

Je nachdem, wie die Indexe aufgebaut sind, ist die erste Abfrage mit dem Subselect vollkommen in Ordnung. Ich nehme mal an, dass die project_id in beiden Spalten einen Index hat.

[Exxtreme]

Mach statt NOT IN (Subquery) lieber NOT EXISTS (Subquery). Das ist meist schneller.

[Matrix316]

Da fehlen durch den inner join schon die projects ohne Kinder und findet dafür alle die mindestens ein Kind mit was anderem als a haben
Ach so. Ich dachte alle haben Kinder. Ja dann mit Left Join.

select a.* from projects a left join divisions b on a.id = b.project_id where b.name <> 'a'

Viele Wege führen bei SQL nach Rom. Da muss man halt probieren. :)

Mach statt NOT IN (Subquery) lieber NOT EXISTS (Subquery). Das ist meist schneller.

Echt? Das muss ich mal probieren. Wobei IMO ein Join meistens die schnellste Lösung ist. Allerdings kommt man manchmal nicht um Unterabfragen rum (oder es ist schneller zu schreiben ;)).

[Rooter]

Wie groß sind denn die beiden Tabellen?

Ich spekuliere mal, dass Exxtremes "NOT EXISTS (Subquery)" der eleganteste Weg ist. Wobei die anderen sicher wenig bis gar nicht langsamer sein werden. Unterschätze nie den Query-Optimizer! ;)

MfG
Rooter

[Exxtreme]

Ja, es kann sein, dass die heutigen Optimizer bei NOT IN und NOT EXISTS den gleichen Ausführungsplan benutzen werden. Es ist aber so, NOT EXISTS ist eine binäre Ja/Nein-Operation. Sie wird sofort abgebrochen beim ersten Erfüllen der Bedingung. NOT IN führt erstmal die Unterabfrage aus und nutzt dann die Werte, die ermittelt wurden. Und es ist auch so, je mehr Werte die Unterabfrage bei NOT IN zurückliefert desto langsamer wird es im Vergleich zu NOT EXISTS.

Aber am besten hier mal ein EXPLAIN machen und schauen wie die Kosten sind.

[Ben Carter]

Ich hab das mal auf sehr, sehr ähnliche Tabellen ausprobiert. Leider habe ich in diesen kaum Daten und bei einem Kundensystem wollte ich es jetzt nicht testen.

MSSQL:
mit NOT EXISTS: 0ms (also quasi nicht messbar)
mit NOT IN: 14ms

Gemessen über SET STATISTICS TIME ON

Ausführungsplan sieht logischerweise anders aus.

[Ganon]

Es ist aber so, NOT EXISTS ist eine binäre Ja/Nein-Operation. Sie wird sofort abgebrochen beim ersten Erfüllen der Bedingung.

"NOT" EXISTS muss aber trotzdem im Hintergrund alle Daten des Resultsets prüfen, im Gegensatz zu EXISTS. Hier könnte aber auch der Optimierer entscheiden, die Bedingung umzustellen.

Ansonsten hab ich auch schon Queries gehabt, die in MySQL eine zweistellige Anzahl an Sekunden brauchte und in PostgreSQL nur wenige Millisekunden. Eine leicht abgeänderte Query hat dann in beiden Systemen ähnliche Performance gebracht.

Ist also schon sinnvoll immer mal mit den verschiedenen Abfragearten rumzuspielen. Man sollte wirklich nicht annehmen, dass jede Datenbank jede mögliche Form der Optimierung durchführt. Auch wenn sie noch so simpel erscheint.

[PatkIllA]

Beim SQL Server sieht der Execution Plan bei EXISTS/NOT EXISTS identisch aus.
Der IN Subquery Plan ist deutlich komplexer.

[Exxtreme]

Es hängt davon ab ob die Spalte, die abgefragt wird, NULLs erlaubt oder nicht. Hier kann es zu Unterschieden bei den Ausführungsplänen kommen. Es gibt auch noch einen weiteren heftigen Unterschied: wenn die Unterabfrage NULLs zurückliefert dann wird die gesamte Abfrage nichts zurückliefern wenn man NOT IN nutzt. Und das ist oft nicht das, was man will. Deshalb ist es IMHO besser standardmäßig NOT EXISTS zu nutzen.

[Matrix316]

Ok "not exists" scheint etwas schneller zu sein als "not in", aber man muss auch die Abfrage anders schreiben. Ich finde aber auch "not in" logischer zu verstehen als "not exists" oder auch "exists". Weil was existiert denn oder nicht? Da muss ich erst mal kurz drüber nachdenken, was will ich eigentlich? Bei "not in" oder "in" ist das irgendwie einfacher zu verstehen was damit gemeint ist. ;)

[PatkIllA]

das mit dem "nicht" dreht es noch mal um und man kriegt nen Knoten im Kopf.

Dafür kann man den Positiv-/Negativfall einfach durch das Wort NOT in der Query anpassen. Habe jetzt beide Fälle durch das EXISTS ersetzt.

Wäre das nicht generell empfehlenswert wenn es einfach nur um die Existenz geht?
Ich müsste das auch noch für Assoziationen mit Zwischentabelle machen. Die Werte aus dem Join brauche ich da erstmal nicht und es geht nur ums Suchen.

[Exxtreme]

Die Sache ist eigentlich einfach. X-D

select mytable.id, mytable.name from mytable where not exists (select * from mytable2 where mytable2.name = 'somename' and mytable2.mytable_id = mytable.id)


Für (NOT) EXISTS ist ausschließlich der rote Teil interessant. Nur diese Bedingung wird geprüft. Lasst euch nicht durch die select-Anweisung in der Unterabfrage verwirren. X-D Was da drin steht, ist völlig irrelevant. Ihr könnt auch "select 1/0" reinschreiben und die Abfrage läuft trotz vermeintlicher Division durch 0 ohne Probleme. Und weil dieser Check auf die bloße Existenz so simpel ist, läuft diese Abfrage auch in aller Regel schneller bis viel schneller. Und je mehr Datensätze die innere Abfrage zurückliefern würde, desto schneller wird das im Vergleich zu NOT IN.

[Matrix316]

... wobei (Not) exists auch nur mit Unterabfragen geht, weil select * from Tabelle where not exists (1,2,3) geht nicht direkt. :ugly: Dafür halt ... where id not in (1,2,3).

[Ganon]

Man muss nur aufpassen, dass je nach Datenbank die Anzahl der Elemente in einem "IN" Operator begrenzt sein kann.