Zur Semantik der ON clause im ANSI Left Outer Join

Vielleicht wäre es sinnvoller, auf diesen Eintrag zu verzichten, weil er ein wenig peinlich ist - aber was soll's: gestern ist mir im Rahmen eines OTN Threads aufgefallen, dass meine Interpretation der ANSI OUTER JOIN Syntax bislang unzutreffend war. Ich verzichte auf nähere Erläuterungen zu meiner Fehleinschätzung (die für meine übliche Formulierung von ANSI OUTER JOINs ganz plausibel war), und erkläre lieber gleich, was das ON tatsächlich bedeutet:

drop table t1;
drop table t2;

create table t1
as
select rownum id
     , 0 col1
  from dual
connect by level <= 10;

create table t2
as
select rownum id
     , 1 col1
  from dual
connect by level <= 5;

-- Fall 1:
select t1.id t1_id
     , t2.id t2_id
  from t1
  left outer join
       t2
    on (t1.id = t2.id)
 order by t1.id;

     T1_ID      T2_ID
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6
         7
         8
         9
        10

-- Fall 2:
select t1.id t1_id
     , t2.id t2_id
  from t1
  left outer join
       t2
    on (t1.id = t2.id and t2.col1 = 0)
 order by t1.id;

     T1_ID      T2_ID
---------- ----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

-- Fall 3:
select t1.id t1_id
     , t2.id t2_id
  from t1
  left outer join
       t2
    on (t1.id = t2.id and t1.col1 = 1)
 order by t1.id;

     T1_ID      T2_ID
---------- ----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

Fall 1 ist dabei völlig harmlos: das ON enthält nur die Join-Bedingung, so dass der OUTER JOIN zu jedem Satz aus t1 den passenden Wert aus t2 verknüpft und die in t2 fehlenden Werte durch NULL ergänzt.

Fall 2 ist auch einleuchtend: in der Join-Bedingung erscheint das Prädikat t2.col1 = 0, das für alle Sätze in t2 false liefert, so dass die T2_ID immer mit NULL gefüllt wird.

Fall 3 ist der, der mir nicht klar war - auch, weil ich noch nicht auf die Idee gekommen war, eine solche Einschränkung im ON zu verwenden. Auch für t1.col1 = 1 gilt, dass die Bedingung immer false ist, aber sie wirkt sich nicht auf die Filterung von t1 aus, sondern führt auch nur dazu, dass die Join-Bedingung für alle Vergleiche false ist, so dass für T2_ID wiederum ausschließlich NULL-Werte erscheinen.

Demnach gilt: die ON clause im LEFT OUTER JOIN definiert, zu welchen Sätzen der Tabelle auf der linken Seite Sätze aus der rechten Tabelle verknüpft werden sollen. Das klingt erst mal selbstverständlich, führt aber nicht unbedingt zu intuitiv verständlichen Bedingungen - sofern man den Mechanismus nicht klar vor Augen hat.

Jonathan Lewis hat mich in diesem Zusammenhang auf einen seiner Artikel verwiesen, in dem er zu einem ähnlichen Beispiel erklärt:

If you’re familiar with ANSI SQL you won’t need more than a couple of moments to interpret the following query – but I have to admit that I had to think about it carefully before I figured out what it was trying to achieve.

Dass auch der Herr Lewis ernsthaft darüber nachdenken musste, ist dann doch wieder beruhigend.

Nebenbei sei noch erwähnt, dass der OTN Thread einen recht massiven Bug für den LEFT OUTER JOIN mit ANSI Syntax in 12c aufzeigt und dass Oracles interne Umformulierung solcher zusätzlicher Bedingungen im ON ziemlich merkwürdig ausfällt.

Und noch ein kleiner Nachtrag: vor einiger Zeit hatte ich hier schon mal über das Thema der OUTER JOIN-Einschränkungen geschrieben, war aber nicht weiter als bis Fall 2 gekommen.

Nachtrag 22.04.2014: auf der Suche nach anderen Dingen bin ich in meinen Archiven auf eine Betrachtung der Semantik des ON im Merge gestoßen, die mir offenbar gelegentlich schon mal ähnliche Kopfschmerzen bereitet hat. Vermutlich sollte ich hier nicht nur Notizen machen, sondern diese bisweilen auch noch mal lesen ...