Select-Abfrage über mehrere Tabellen

Mich würde auch mal interessieren, wie man das ganze kompakt aufschreibt.

Ich habe jetzt im Endeffekt für ein analoges Problem zwei fast identische Anfragen über ein INTERSECT miteinander verknüpft, aber das muss doch effizienter funktionieren? Ich wollte das ursprünglich mit EXISTS lösen, kam da aber überhaupt nicht weiter.

asc

Weitere Möglichkeit (ungetestet):

SELECT k.KdName
FROM db.Kunde k
WHERE EXISTS
( SELECT e.*
  FROM db.Einkauf e
  WHERE k.KdNr = e.KdNr
  AND (e.ArtNr = 2 OR e.ArtNr = 3))

Oder

SELECT k.KdName
FROM db.Kunde k
WHERE EXISTS
( SELECT e.*
  FROM db.Einkauf e
  WHERE k.KdNr = e.KdNr
  AND e.ArtNr = 2)
UNION
SELECT k.KdName
FROM db.Kunde k
WHERE EXISTS
( SELECT e.*
  FROM db.Einkauf e
  WHERE k.KdNr = e.KdNr
  AND e.ArtNr = 3)

Klappt leider beides nicht (mit PostgreSQL getestet), liefert alle Kunden zurück, die entwerder Produkt A und / oder Produkt B gekauft haben.

Um ehrlich zu sein, verstehe ich auch nicht so ganz, wie EXISTS genau funktionieren soll. Ich hätte es nämlich genauso geschrieben und für mich macht das so auch Sinn.

hustbaer

Mit nur einem EXISTS kann es nicht gehen, da kommen klarerweise immer die Kunden zurück die einen ODER den anderen Artikel gekauft haben.
Wenn dann braucht man zwei EXISTS

SELECT k.KdName
FROM Kunde k
WHERE EXISTS (SELECT * FROM Einkauf AS e1 WHERE e1.KdNr = k.KdNr AND e1.ArtNr = 2)
  AND EXISTS (SELECT * FROM Einkauf AS e2 WHERE e2.KdNr = k.KdNr AND e2.ArtNr = 3)

(On die Unterschiedlichen Table-Alias (e1, e2) nötig sind, weiss ich nicht. Sollte vermutlich auch mit 2x bloss "e" funktionieren, nur da ich mir nicht sicher bin...)

Alternativ könnte man mit GROUP BY und HAVING COUNT(*) = 2 arbeiten. Würde ich aber nur machen, wenn die Artikelzahl auch mal gross werden kann (mehr als nur 2-3 verschiedene Artikel).

Beispiel:

SELECT k.KdNr, k.KdName
FROM Kunde AS k
    INNER JOIN
        (SELECT DISTINCT KdNr, ArtNr FROM Einkauf WHERE ArtNr IN (2, 3)) AS e
        ON e.KdNr = k.KdNr
GROUP BY k.KdNr, k.KdName
HAVING COUNT(*) = 2

Oder so ähnlich.
(Wenn das DISTINCT bereits so garantiert ist, dann kann das hässliche Sub-Select natürlich weg)

EDIT: 2. SQL Schnippsel korrigiert.

Nochmal EDIT: es geht natürlich auch mit 2x LEFT JOIN und IS NOT NULL, sollte klar sein. (EXISTS kann man ja oft mit LEFT JOIN und IS NOT NULL ersetzen. Ich finde die EXISTS Schreibweise aber besser verständlich, daher spar ich mir auch das LEFT JOIN Beispiel)

pumuckl

Nur zwei simple joins auf die Kundennummer reichen doch:

SELECT k.KdName FROM Einkauf e1, Einkauf e2, Kunden k
-- join auf KdNr
WHERE e1.KdNr = k.KdNr
AND e2.KdNr = k.KdNr
AND e1.ArtNr = 2 
AND e2.ArtNr = 3

Sollte performanter sein als subselects, having, exists und der ganze Kram.

hustbaer

Sollte performanter sein als subselects, having, exists und der ganze Kram.

Kommt auf den Server an. Mit MSSQL ist das hübsch egal. Also zumindest JOIN vs. EXISTS nimmt sich da nix.

pumuckl

hustbaer schrieb:

Sollte performanter sein als subselects, having, exists und der ganze Kram.

Kommt auf den Server an.

Kommt auch auf Indizes an und wie der Server damit und mit eventuell vorhandenen Statistiken zurandekommt. Auf meiner DB2 hat ein Index auf KdNr in beiden Tabellen gereicht, um das Ganze verdammt fix zu machen. Das Exists hat den index dagegen rausgehauen, da war ein full table scan nötig...

hustbaer

Versteh jetzt nicht was du meinst.
Ohne Index ist immer lahm.

Und mit passenden Indexen kommts drauf an wie schlau der Query-Optimizer des verwendeten Servers ist. Genau das meinte ich mit "kommt auf den Server an".

Das sollte auch funktionieren:

SELECT KdName
FROM Kunde INNER JOIN
(
  SELECT KdNr ,GROUP_CONCAT(ArtNr) AS Artikel 
  FROM Einkauf GROUP BY KdNr
) AS E
ON Kunde.KdNr = E.KdNr
WHERE Find_In_Set(2,Artikel) AND Find_In_Set(3,Artikel)

pumuckl

hustbaer schrieb:

Versteh jetzt nicht was du meinst.
Ohne Index ist immer lahm.

Ja, aber es gibt Dinge, die den index nutzlos machen, weil die DB dann trotz Index einen Full Table Scan machen muss. Exists gehört dazu.

hustbaer

pumuckl schrieb:

hustbaer schrieb:

Versteh jetzt nicht was du meinst.
Ohne Index ist immer lahm.

Ja, aber es gibt Dinge, die den index nutzlos machen, weil die DB dann trotz Index einen Full Table Scan machen muss. Exists gehört dazu.

Öh. Nö Das stimmt nicht.
MS SQL Server kann EXISTS wunderbar optimieren, kein unnötiger Table-Scan.

// 1
SELECT * FROM a WHERE EXISTS (SELECT * FROM b WHERE b.nr = b.nr)

// 2
SELECT * FROM a WHERE nr IN (SELECT nr FROM b)

// 3
SELECT a.* FROM a
    INNER JOIN b ON b.nr = a.nr

Ergeben alle den *exakt* gleichen Execution-Plan.

EDIT: Fehler korrigiert. Bei (3) muss es SELECT a.* heissen damit genau der selbe Execution-Plan rauskommt, nicht einfach SELECT *. Ist auch logisch. /EDIT

asc

pumuckl schrieb:

Ja, aber es gibt Dinge, die den index nutzlos machen, weil die DB dann trotz Index einen Full Table Scan machen muss. Exists gehört dazu.

Fakt ist, das solche Aussagen immer auf spezifische Datenbanken beschränkt sind. Wir hatten einige Fälle (Oracle) wo EXISTS sogar schneller als die Alternativen waren. Das hängt natürlich auch an mehreren Faktoren.