JOINs und zusätzliche Bedingungen

[Jürgen Thomas]

Hallo,

zwei Fragen des allgemeinen Verständnisses:

Bei JOINs werden durch ON vorzugsweise PrimaryKey der einen Tabelle mit einem ForeignKey der anderen Tabelle verbunden. Außerdem ist es möglich, diese Vergleichsbedingung durch andere Suchbedingungen zu ergänzen:
JOIN tabelle2 ON tabelle2.FK = tabelle1.PK AND tabelle2.ID < 10 Dazu habe ich folgende Fragen:

1. Diese ergänzenden Bedingungen könnten genausogut unter WHERE stehen. Ist es sinnvoller, sie dann unter JOIN ON aufzuführen, wenn sie sich auf tabelle2 beziehen? Gibt es noch andere Kriterien für die Entscheidung, wo eine ergänzende Bedingung besser stehen sollte?
2. Kann mir jemand ein sinnvolles Beispiel nennen, wo im ON-Teil eine beliebige Suchbedingung mit <=, IN oder LIKE steht, ohne dass eine PK/FK-Verknüpfung vorgesehen ist? Oder gibt es so etwas zwar in der Theorie, aber niemals in der Praxis?

Danke für Erläuterungen! Jürgen

[mkinzler]

Im on sollte man nur Join-Bedingungen abhandeln

[PMM]

Man sollte sich davon leiten lassen, wozu die einzelnen Bereiche gedacht sind:
- FROM: beschreibt die Struktur der zu liefernden Datenmenge (z.B. via JOIN)
- WHERE: schränkt die zurückzuliefernde Datenmenge auf die akt. erforderlichen ein
PMM

[p80286]

Hallo Jürgen,

ergänzend kann ich Dir sagen das es mir schon einmal untergekommen ist, das ein JOIN über einen Substring realisiert wurde. Aber das sind irgendwelche Hackerlösungen. Wenn die DB ordentlich ausgearbeitet wurde, dann werden alle Tabellenrelationen über ID-Keys erledigt. Sobald ein String ins Spiel kommt, dann hat man ein Problem mit dem Zeichensatz und/oder der Groß/Kleinschreibung.

Gruß
K-H

[Jürgen Thomas]

Danke für die Antworten.

Mich hatte die SQL-Doku etwas verwirrt, wo es allgemein heißt:

Zitat:

<qualified join> ::= <table reference> [ <join type> ] JOIN <table reference> <join specification>
<join specification> ::= <join condition> | <named columns join>
<join condition> ::= ON <search condition>

Das heißt, die Bedingung hinter dem ON könnte eine beliebige Suchbedingung (einschließlich AND usw.) sein.

Auch bei der Suche hier im Forum fand ich vereinzelt Beispiele mit ON ( ... AND ...). Das hat mich zu meiner Nachfrage veranlasst. Ich ziehe daraus die Konsequenz:

* Hinter ON kommt nur die Vergleichsbedingung, also PK = FK (FK soweit vorhanden, sonst ähnliche Verknüpfung).
* Alle Auswahlbedingungen gehören zu WHERE.

(Es sei denn, es kommt noch eine qualifizierte abweichende Äußerung.)

Gruß Jürgen

[DeddyH]

In die JOIN-Klausel gehören die Bedingungen zur Verknüpfung der Tabellen (das können auch mehrere sein), in die WHERE-Klausel die Bedingungen zur Filterung der Datensätze.

[alex517]

Hi,

ich denke es ist durchaus manchmal notwendig im "ON" außer der Join- auch
andere Bedingungen anzugeben.

folgendes Bsp.
Gesucht sind ALLE Personen mit der Anzahl der zugeordneten Verträge:

SQL-Code:

select
  P.ID,
  P.PERSONEN_NAME,
  count(A.ID)
from
  PERSON P
  left join EAUFTRAG A on (A.PATIENT_ID = P.ID)
group by
  P.ID,
  P.PERSONEN_NAME

Im Ergebnis erhält man alle Personen in der Anzahl zugeordnete Verträge,
inclusive der Personen mit 0 Aufträgen.

Wenn man jetzt wieder ALLE Personen und die Anzahl Verträge alledings
begrenzt auf das einen Zeitraum (z.B. 2007) sehen will, könnte man
als Erstes auf die Idee kommen einfach in der Where-Klausel den
Zeitraum der Auftrage einzugrenzen:

SQL-Code:

select
  P.ID,
  P.PERSONEN_NAME,
  count(A.ID)
from
  PERSON P
  left join EAUFTRAG A on (A.PATIENT_ID = P.ID)
where
  (A.DATUM_VON between '01.01.2007' and '31.12.2007')
group by
  P.ID,
  P.PERSONEN_NAME

Damit bekommt man jetzt zwar alle Personen die mindestens eine Vertrag
in 2007 haben, aber alle anderen Personen ohne Vertrage (count(A.ID) = 0) fehlen!

Warum? Weil in der "verjointen" Ergebnismenge, über diese wird das count() ausgeführt,
nur Daten enthalten sind für die (A.DATUM_VON between '01.01.2007' and '31.12.2007') zutrifft.
Mit dieser Bedingung wird das "left join" sozusagen ausgehebelt.

Gut, also müssen in der "verjointen" Ergebnismenge auch Datensätzen mit A.DATUM_VON is NULL,
also Personen ohne Vertrag, enthalten sein.
Die Where-Klausel wird erweitert:

SQL-Code:

select
  P.ID,
  P.PERSONEN_NAME,
  count(A.ID)
from
  PERSON P
  left join EAUFTRAG A on (A.PATIENT_ID = P.ID)
where
  A.DATUM_VON is null or (A.DATUM_VON between '01.01.2007' and '31.12.2007')
group by
  P.ID,
  P.PERSONEN_NAME

Damit erhält man genau das gewünschte Ergebnis:
Alle Personen mit der Anzahl Verträge im Jahr 2007 incl. Personen ohne Verträge.

Wenn man sich jetzt (z.B. im IBExpert) die Anzahl der Read-Zugriffe ansieht,
erhält man im Beispiel
Person, nicht indiziert Reads: 167
EAuftrag, indiziert Reads: 644

Stellt man jetzt aber die SQL um, in dem man bereits im "..ON.." den Zeitraum einschränkt:

SQL-Code:

select
  P.ID,
  P.PERSONEN_NAME,
  count(A.ID)
from
  PERSON P
  left join EAUFTRAG A on (A.PATIENT_ID = P.ID and A.DATUM_VON between '01.01.2007' and '31.12.2007')
group by
  P.ID,
  P.PERSONEN_NAME

so erhält man die gleiche Ergebnismenge aber mit folgende Read-Zugriffe:
Person, nicht indiziert Reads: 167
EAuftrag, indiziert Reads: 65

Warum?
Weil die Anzahl der Daten aus EAUFTRAG bereits VOR dem JOIN eingeschränkt wurde!
Es müssen also erheblich weniger Daten gezählt werden.


alex

[Jürgen Thomas]

Danke für die Informationen und Meinungen.

Alex' Beispiel ist nützlich und verweist darauf, dass Abweichungen von der Regel immer mal vorkommen können. Allerdings kann man gerade in diesem Fall die beiden Tabellen vertauschen und einen RIGHT JOIN benutzen. (Ich habe jetzt allerdings nicht getestet, wie es mit der Performance aussieht.)

Es bleibt also bei dem, was ich schon als "Konsequenz" formuliert habe und Detlef bestätigt hat - mit der Einschränkung, dass man in einer konkreten Situation auch mal anders verfahren kann, sofern das sehr gut begründet wird.

Gruß Jürgen