Wird LEFT OUTER JOIN durch WHERE-Bedingung ausgeschalten?

Hallo zusammen,

ich habe gerade einen komischen Effekt bei einem LEFT OUTER JOIN in postgres festgestellt. Leider hab ich im Moment keine andere Datenbank zur Hand, an der ich dieses Verhalten ausprobieren könnte.

Sinn eines LEFT OUTER JOIN ist es ja, in der Ergebnistabelle für jede Zeile der linken Tabelle mindestens einen Eintrag zu bekommen, auch wenn in der rechten Zeile keine JOIN-Bedingung zugetroffen hat. Das funktioniert auch, solange ich keine WHERE-Bedingung mehr einbaue. Nehmen wir an ich habe 2 Tabellen genannt Vater und Sohn mit folgendem Aussehen:

Vater:

id     Name
---------
1      Anton
2      Horst
3      Peter

Sohn:

Name         Vater_id
-----------------------
Helmut       2
Paul         3

Nur Horst und Peter haben also Söhne, Anton nicht. Ein LEFT OUTER JOIN würde dann zu folgendem Ergebnis führen:

SELECT * FROM Vater LEFT OUTER JOIN Sohn ON (Vater.id = Sohn.Vater_id);

id     Name      Name         Vater_id
---------
1      Anton
2      Horst     Helmut       2
3      Peter     Paul         3

d.h. Anton wird angezeigt, aber eben ohne Sohn, so wie es sein soll. Wird die Abfrage nun um eine WHERE-Bedingung erweitert, so funktioniert dies nicht mehr:

SELECT * FROM Vater LEFT OUTER JOIN Sohn ON (Vater.id = Sohn.Vater_id) WHERE Vater.id <3;

Die die Vater.id soll also kleiner 3 sein, d.h. nur Anton und Horst sollen aufgelistet werden.

Die Abfrage ergibt jedoch:

id     Name      Name         Vater_id
---------
2      Horst     Helmut       2

Anton ist also aus dem Ergebnis verschwunden, obwohl er eine id < 3 hat.

Ist das ein Standardkonformes Verhalten??? Ich kann mich da echt nimmer dran erinnern. Zu umgehen ist das Ganze mit einem Subselect wie z.B.:

SELECT * FROM (SELECT * FROM Vater WHERE id < 3) AS temp LEFT OUTER JOIN Sohn ON (temp.id = Sohn.Vater_id);

Das Kann bei größeren Anfragen jedoch leicht vollkommen unüberschaubar und fast schon unmöglich werden. Deshalb eben die Frage: Ist dieses Verhalten Standardkonform, woran liegt das und gibt es einen schlaueren Weg?

Viele Grüße,
Bierstuebl



test=# create table vater (id int, name text);
CREATE TABLE
Time: 69.499 ms
test=*# copy vater from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1    Anton
>> 2    Horst
>> 3    Peter
>> \.
Time: 18175.113 ms
test=*# commit;
COMMIT
Time: 5.012 ms
test=# create table sohn (name text, vater_id int);
CREATE TABLE
Time: 53.632 ms
test=*# copy sohn from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> Helmut       2
>> Paul 3
>> \.
Time: 15481.880 ms
test=*# commit;
COMMIT
Time: 49.933 ms
test=# SELECT * FROM Vater LEFT OUTER JOIN Sohn ON (Vater.id = Sohn.Vater_id);
 id | name  |  name  | vater_id
----+-------+--------+----------
  1 | Anton |        |
  2 | Horst | Helmut |        2
  3 | Peter | Paul   |        3
(3 rows)

Time: 2.180 ms
test=*# SELECT * FROM Vater LEFT OUTER JOIN Sohn ON (Vater.id = Sohn.Vater_id) WHERE Vater.id <3;
 id | name  |  name  | vater_id
----+-------+--------+----------
  1 | Anton |        |
  2 | Horst | Helmut |        2
(2 rows)

Time: 10.866 ms
test=*# select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6)
(1 row)

Andreas

Hallo Andreas,

sorry, war die ganze Zeit nicht im Lande. Du hast natürlich recht - der Fehler lag darin, in der WHERE-Bedingung auch noch ein Feld der rechten Tabelle einzubauen, z.B. etwas wie sohn.name like ‘n%’ oder so. Damit fallen dann alle Datensätze aus dem left outer join raus, die eben nur in der linken Tabelle vorhanden sind.