SQL - Filter in ON-Klausel vs. WHERE-Klausel

[Zvoni]

Ich hatte jetzt schon in diversen Threads eine "Meinungsverschiedenheit" mit Yaslaw zu im Betreff genanntem Thema.
(Und "Meinungsverschiedenheit" nicht im Sinne von "Streit", sondern wortwörtlich: Unterschiedliche Meinungen).
Bitte nicht falsch verstehen: Ich habe höchsten Respekt vor Yaslaw und seinem Wissen und Fähigkeiten.

hab jetzt mal nen eigenen Thread aufgemacht hier, weil ich denke, dass es doch für den ein oder anderen dann nützliche Information darstellen kann.

Begriffe:
Was ist ein "Filter"?
Ein Filter in einem SQL-Statement ist eine Bedingung, welche vom Anwender mit einem (festen) Wert initiiert wird.
Beispiel:

SELECT ....... FROM Tabelle WHERE EinFeld=1  /*oder per Parameter*/

Die "Meinungsverschiedenheit" zwischen mir und Yaslaw war, dass Yaslaw einen Filter in einer ON-Klausel ablehnt, während ich der Meinung bin, dass es vollkommen zulässig ist.

Ich hab daraufhin diese Frage in einem anderen Forum gestellt, habe auf den Antworten von dort basierend einen kleinen Test durchgeführt, und bin zu folgenden Ergebnis gekommen:
1) In einem INNER JOIN ist es egal, wo der Filter steht. Das Ergebnis ist dasselbe, ob der Filter in der ON-Klausel oder in der WHERE-Klausel steht. (In unten genanntem Beispiel einfach mal den LEFT JOIN mit einem INNER JOIN ersetzen)
2) Bei einem OUTER JOIN (Bsp. LEFT JOIN) ist es nicht egal!
Setze ich den Filter in die ON-Klausel, wird zuerst gefiltert, dann gejoint
Setze ich den Filter in die WHERE-Klausel, wird erst gejoint, dann gefiltert.
Die Ergebnis-Menge ist unterschiedlich.
3) Es absolut zulässig (auch im Rahmen der Logik) in der ON-Klausel einen Filter zu setzen.

Meinungen?

Für meinen Test (in SQLite) habe ich folgende Basis benutzt:

CREATE TABLE "Jobs" ("ID" INTEGER, "Description" TEXT);
CREATE TABLE "Jobs2" ("ID" INTEGER,"Description2" TEXT);

INSERT INTO "main"."Jobs" ("ID", "Description") VALUES ('1', 'Job1');
INSERT INTO "main"."Jobs" ("ID", "Description") VALUES ('2', 'Job2');
INSERT INTO "main"."Jobs" ("ID", "Description") VALUES ('3', 'Job3');
INSERT INTO "main"."Jobs" ("ID", "Description") VALUES ('4', 'Job4');
INSERT INTO "main"."Jobs" ("ID", "Description") VALUES ('5', '');
INSERT INTO "main"."Jobs" ("ID", "Description") VALUES ('6', 'Job6');
INSERT INTO "main"."Jobs" ("ID", "Description") VALUES ('7', 'Job7');
INSERT INTO "main"."Jobs" ("ID", "Description") VALUES ('8', '');
INSERT INTO "main"."Jobs" ("ID", "Description") VALUES ('9', 'Job9');
INSERT INTO "main"."Jobs2" ("ID", "Description2") VALUES ('1', 'Job1');
INSERT INTO "main"."Jobs2" ("ID", "Description2") VALUES ('2', 'Job2');
INSERT INTO "main"."Jobs2" ("ID", "Description2") VALUES ('3', 'Job3');
INSERT INTO "main"."Jobs2" ("ID", "Description2") VALUES ('4', 'Job2');
INSERT INTO "main"."Jobs2" ("ID", "Description2") VALUES ('6', 'Job6');
INSERT INTO "main"."Jobs2" ("ID", "Description2") VALUES ('7', 'Job2');
INSERT INTO "main"."Jobs2" ("ID", "Description2") VALUES ('9', 'Job9');

hier die zwei Testabfrage, bei welchen man den Unterschied sieht

SELECT T1.ID, T1.Description, T2.ID, T2.Description2
FROM Jobs AS T1 LEFT JOIN Jobs2 AS T2
ON T1.ID=T2.ID AND T2.Description2='Job2'

SELECT T1.ID, T1.Description, T2.ID, T2.Description2
FROM Jobs AS T1 LEFT JOIN Jobs2 AS T2
ON T1.ID=T2.ID
WHERE T2.Description2='Job2'

 

[Yaslaw]

Da lese ich jetzt gerne gespannt zu.
Das erste Select entspricht übrigens dem hier.

SELECT T1.ID, T1.Description, T2.ID, T2.Description2
FROM Jobs AS T1
    LEFT JOIN (
        SELECT *
        from Jobs2
        WHERE Description2='Job2'
    ) AS T2
ON T1.ID=T2.ID;

Bei komplexeren SQLs würde ich diese Schreibweise vorziehen. Oder noch besser mit WITH arbeiten. Das ist am einfachsten um A) Fehler zu finden, B) zu verstehen
Ist natürlich meine Meinung

WITH j2 AS (
    SELECT *
    from Jobs2
    WHERE Description2='Job2'
)
SELECT T1.ID, T1.Description, T2.ID, T2.Description2
FROM Jobs AS T1
    LEFT JOIN j2 AS T2
        ON T1.ID=T2.ID;

Nachtrag:
Ich bin übrigens auch nicht zu 100% Konsequent. Ein INNER JOIN verknüpfe ich meistens im WHERE.

SELECT T1.ID, T1.Description, T2.ID, T2.Description2
FROM Jobs AS T1, Jobs2 AS T2
WHERE 
    T1.ID=T2.ID
    AND T2.Description2='Job2';

 

[Zvoni]

Da lese ich jetzt gerne gespannt zu.
Das erste Select entspricht übrigens dem hier.

SELECT T1.ID, T1.Description, T2.ID, T2.Description2
FROM Jobs AS T1
    LEFT JOIN (
        SELECT *
        from Jobs2
        WHERE Description2='Job2'
    ) AS T2
ON T1.ID=T2.ID;

Wusst ichs doch *ggg*

OK, zu deinem Vorschlag: Ja, natürlich geht das auch so, und ich gebe dir für dieses Szenario auch Recht, da es:
1) Deutlicher zu sehen ist, was passiert (erst filtern, dann JOIN)
2) Für "echte" Spalten natürlich einfacher ist

Die Verwendung eines Filters in der ON-Klausel bin ich erst darauf gestossen (bzw. wurde mehr oder weniger dazu gezwungen), als ich mit Feld-Aliasen arbeiten musste, insbesondere Aliase für Aggregat- bzw. Window-Funktionen (in meinem konkreten Fall ROW_NUMBER), und da wirds bekanntlich spannend mit Aliasen im WHERE-Abschnitt (bzw. Alias im HAVING-Abschnitt für Funktionen).

Um aber dein o.g. Verfahren tatsächlich auch nutzen zu können ist man aber gezwungen einen "SUB-Select" zu schreiben, anstelle tatsächlich und einfach auf die rechte Tabelle zu joinen wie in meinem Beispiel (Und mal Hand aufs Herz: Wieviele von uns machen denn eher ein

SELECT Tabelle1.Feld1, Tabelle2.AnderesFeld2
FROM Tabelle1 LEFT JOIN Tabelle2
ON Tabelle1.ID=Tabelle2.ID

)

Bin gespannt auf weitere Meinungen

EDIT: Das mit den CTE ist natürlich auch so ne Sache.
AFAIK, alle gängigen Datenbanken unterstützen CTE, jedoch irgendwie alle mit einer irgendwie eigenen Syntax/Logik. Ich glaube mich daran zu erinnern, dass es z.B. deutliche Unterschiede zwischen MySQL und MSSQL gibt (kann mich aber auch täuschen). Ganz zu schweigen, dass anscheinend nicht alle DBMS z.B. rekursive CTE unterstützen.
Einen Filter in der ON-Klausel zu setzen sollte in allen DBMS gehen, da es ANSI-SQL-Standard ist