Outer Join mit (+)

[Kanubelkarl]

Hi, ich habe gerade ein kleines Problem mit einem Statement das icht nicht ganz verstehen, also bräuchte eine erklärung.

Die einfache Variante die ich auch verstehe ist
select artikelnummer, artikel from artikel
where lieferant not in (select lieferant_ID from lieferant where ort = 'Berlin')
or lieferant is null;

Also das mir alle Artikelnummern mit Artikel angezeigt werden, wo der Lieferant nicht aus Berlin ist, oder die keinen Lieferant haben. Jetzt habe ich noch ein Statement das schneller gehen soll.

select a.artikelnummer, a.artikel, from artikel a, lieferant l
where a.lieferant = l.lieferant_ID (+)
and l.lieferant_id is null
and l.ort (+)= 'Berlin'
order by a.artikelnummer;

laut unserem Lehrer soll beides Funktionieren, jedoch ist mit nicht klar wieso das funktioniert.
PS:Mein Lehrer weis es auch nicht so recht

Grüße
Kanubelkarl

 

[akretschmer]

Hi, ich habe gerade ein kleines Problem mit einem Statement das icht nicht ganz verstehen, also bräuchte eine erklärung.

Die einfache Variante die ich auch verstehe ist
select artikelnummer, artikel from artikel
where lieferant not in (select lieferant_ID from lieferant where ort = 'Berlin')
or lieferant is null;

Also das mir alle Artikelnummern mit Artikel angezeigt werden, wo der Lieferant nicht aus Berlin ist, oder die keinen Lieferant haben. Jetzt habe ich noch ein Statement das schneller gehen soll.

select a.artikelnummer, a.artikel, from artikel a, lieferant l
where a.lieferant = l.lieferant_ID (+)
and l.lieferant_id is null
and l.ort (+)= 'Berlin'
order by a.artikelnummer;

laut unserem Lehrer soll beides Funktionieren, jedoch ist mit nicht klar wieso das funktioniert.
PS:Mein Lehrer weis es auch nicht so recht

Grüße
Kanubelkarl

Das erstere ist ein Subselect, das andere ein Join. Joins sind oft, nicht immer, schneller. Schau Dir das Explain zu beiden Varianten an, dann wirst Du wohl unterschiedliche Pläne und Kosten sehen.

 

[Kanubelkarl]

Ok, aber beim zweiten ist habe ich immer noch etwas Problem beim verstehen. Die WHERE beginnt ja mit einem join zwischen den Tabellen Artikel und Lieferant. Aber dann werden durch das "and l.lieferant_id is null" ja nur die joins genommen bei denen die id null ist, dann durch "and l.ort(+)= 'Berlin" alle bei deinen der ort Berlin ist oder keiner vorhanden ist. Aber ich will ja eigentlich die, wo der Lieferant nicht aus Berlin ist, oder die keinen Lieferant haben.

 

[akretschmer]

Ok, aber beim zweiten ist habe ich immer noch etwas Problem beim verstehen. Die WHERE beginnt ja mit einem join zwischen den Tabellen Artikel und Lieferant. Aber dann werden durch das "and l.lieferant_id is null" ja nur die joins genommen bei denen die id null ist, dann durch "and l.ort(+)= 'Berlin" alle bei deinen der ort Berlin ist oder keiner vorhanden ist. Aber ich will ja eigentlich die, wo der Lieferant nicht aus Berlin ist, oder die keinen Lieferant haben.

Ich komme, um ehrlich zu sein, mit dieser kruden Oraggle (+) - Syntax nicht klar. Im Where steht zum einen die Join-Condition und zum anderen das 'klassische' Where. Irgendwie sieht das verwirrend aus, ich habe auch kein Oraggle, um das zu testen.

Für das, was Du willst, sehe ich (mindestens) diese 2 Wege, die 'richtige' SQL-Syntax sind:

test=*# select * from lieferant ;
 lieferant_id |  ort
--------------+---------
  1 | Berlin
  2 | Dresden
(2 rows)

test=*# select * from artikel ;
 artikelnummer | artikel  | lieferant
---------------+----------+-----------
  1 | artikel1 |  1
  2 | artikel2 |  2
  3 | artikel3 |
(3 rows)

test=*# select artikelnummer, artikel from artikel where lieferant not in (select lieferant_id from lieferant where ort = 'Berlin') or lieferant is null;
 artikelnummer | artikel
---------------+----------
  2 | artikel2
  3 | artikel3
(2 rows)

test=*# select a.artikelnummer, a.artikel from artikel a left join lieferant l on a.lieferant=l.lieferant_id where l.ort != 'Berlin' or a.lieferant is null;
 artikelnummer | artikel
---------------+----------
  2 | artikel2
  3 | artikel3
(2 rows)

 

[Distrilec]

select a.artikelnummer, a.artikel, from artikel a, lieferant l
where a.lieferant = l.lieferant_ID (+)
and l.lieferant_id is null
and l.ort (+)= 'Berlin'
order by a.artikelnummer;

Und das funktioniert auch ? (Ausprobiert?)

Das sollte das Equivalent sein:

Select a.artikelnummer,
       a.artikel
From   artikel a
Where  l.lieferant_id Is Null
Left Join lieferant l On a.lieferant = l.lieferant_id
And  l.ort = 'Berlin'
Order by a.artikelnummer;

Und ich würde bezweifeln dass das funktioniert ^^

(Aber man bedenke... Ich hatte noch keinen Kaffee)

 

[Distrilec]

Select a.artikelnummer,
       a.artikel
From   artikel a,
       lieferant l
Where  a.lieferant = l.lieferant_id(+)
And    l.lieferant <> 'Berlin'
Order  By a.artikelnummer;

So... Das sollte funktionieren

 

[Kanubelkarl]

So, ich habe mir jetzt extra nochmal Oracle drauf gemacht und alles durch getestet.
Es funktionieren beide Statements mit gleichem ergebnis. Aber ich versteh einfach nicht, wie das zweite Funktioniert.

select a.artikelnummer, a.artikel, from artikel a, lieferant l
where a.lieferant = l.lieferant_ID (+)
and l.lieferant_id is null
and l.ort (+)= 'Berlin'
order by a.artikelnummer;

Angezeigt werden alle Artikelnummern mit Artikel, bei denen der Lieferant nicht aus Berlin ist, oder die keinen Lieferant haben.

 

[Distrilec]

Ich hätte dir geglaubt, wenn du geschrieben hättest:
"Angezeigt werden alle Artikelnummern mit Artikel, bei denen der Lieferant keinen Ort zugewiesen hat oder der Lieferant Null ist."

Aus der Oracle Doku

"If the WHERE clause contains a condition that compares a column from table B with a constant, then the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated nulls for this column."
Quelle: http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm#SQLRF52338

 

[Kanubelkarl]

Ja, so habe ich das auch gedacht, was auch Funktioniert, nur wird bei meinem statement das irgentwie umgekehrt. Ich habe jetzt mal alles aus meiner Datenbank raus kopiert

SQL> select * from lieferant;

LIEFERANT_ID NAME STRASSE HAUSN PLZ ORT

-------------------------------- --------------- ------------------------- ----- ---------- -------------------------

AAA Berlini Berlinerweg 17 12345 Berlin
BBB Schlosser Schlosserweg 18 23456 Schlosserhausen
CCC Fischer Fischersteig 19 34567 Fischerdorf

SQL> select * from artikel;

ARTIKELNUMMER ARTIKEL ANZAHL LIEFERANT
------------- ------------------------------ ---------- ---------------------------------
10000 Rasenmäher Schafherde CCC
10001 Rasenmäher Nagelschere BBB
10002 Rasenmäher Elektro BBB
10003 Rasenmäher Hand BBB
10100 Heckenschere Elektro BBB
10101 Heckenschere Hand klein BBB
10102 Heckenschere Hand Teleskop BBB
10103 Heckenschere Benzin BBB
11000 Pflanze Heckenrose AAA
11001 Pflanze Kletterrose AAA
11002 Pflanze Rose rot AAA
11003 Pflanze Rose gelb AAA
11100 Pflanze Tulpe weiß AAA
11101 Pflanze Tulpe gelb AAA
11102 Pflanze Tulpe rot AAA
11103 Pflanze Tulpe dunkelrot AAA
12000 CCC
12001 Rindenmulch klein
12002 Blumenerde klein
12003 Blumenerde groß


SQL> select artikelnummer, artikel from artikel
2 where lieferant not in
3 (select lieferant_id from lieferant where ort ='Berlin')
4 or lieferant is null;

ARTIKELNUMMER ARTIKEL
------------- ------------------------------
10000 Rasenmäher Schafherde
10001 Rasenmäher Nagelschere
10002 Rasenmäher Elektro
10003 Rasenmäher Hand
10100 Heckenschere Elektro
10101 Heckenschere Hand klein
10102 Heckenschere Hand Teleskop
10103 Heckenschere Benzin
12000
12001 Rindenmulch klein
12002 Blumenerde klein
12003 Blumenerde groß


SQL> select a.artikelnummer, a.artikel from artikel a, lieferant l
2 where a.lieferant = l.lieferant_id (+)
3 and l.lieferant_id is Null
4 and l.ort (+)= 'Berlin'
5 order by a.artikelnummer;

ARTIKELNUMMER ARTIKEL
------------- ------------------------------
10000 Rasenmäher Schafherde
10001 Rasenmäher Nagelschere
10002 Rasenmäher Elektro
10003 Rasenmäher Hand
10100 Heckenschere Elektro
10101 Heckenschere Hand klein
10102 Heckenschere Hand Teleskop
10103 Heckenschere Benzin
12000
12001 Rindenmulch klein
12002 Blumenerde klein
12003 Blumenerde groß

 

[Distrilec]

Mit etwas Kaffee geht alles

Selektiert alle Artikel und (wenn Ort = 'Berlin') werden Lieferantendaten hinzugefügt

Select a.*, l.*
From  artikel a,
  lieferant l
Where  a.lieferant = l.lieferant_id
And  l.ort (+) = 'Berlin';

Selektiert davon nur die Artikel, die keine Lieferant_Id haben

Select a.*, l.*
From  artikel a,
  lieferant l
Where  a.lieferant = l.lieferant_id
And  l.ort (+) = 'Berlin'
And  l.lieferant_id Is Null;

(Zur veranschaulichung habe ich noch das l.* hinzugefügt. Wird dann beim ausführen etwas ersichtlicher.)

Hier vllt. nochmal etwas "aufgedröselt" als equivalentes Left Join:

Select t.*
From  (Select a.*, l.*
  From  artikel a
  Left Join lieferant l
  On  a.lieferant = l.lieferant_id
  And  l.lieferant_id = 'Berlin') t
Where  t.lieferant_id Is Null;

 

[Kanubelkarl]

Ah, vielen dank, ich hab es endlich verstanden.
Im Grunde wird durch das l.ort (+) = 'Berlin' als Ort Berlin genommen und bei allen anderen steht sozusagen NULL in den Lieferantenfeldern, und mit l.lieferant_id is Null werden dann die Einträge raus genommen die noch Berlin drin stehen haben
Also vielen dank für die Hilfe

 

[Distrilec]

Kein Problem