left join NULL

[jangbu]

Hallo,

habe folgende abfrage

select a.menge as bestand, b.menge as reserviert, (a.menge - b.menge) as diff
from tab1 a left join tab2 b on (a.name = b.name)

Bekomme auch für alle Artikel (tab1) die Differenz, außer bei den Artikeln wo keine reservierung besteht (kein Datensatz in Tab2), da bekomme ich diff = NULL. Klar da menge - Null = Null

richtig wäre aber:

menge - NULL = menge

Wie bekomme ich das hin?

Danke
jangbu

[mkinzler]

Nach Null abfragen ( IIF(), CASE..WHEN, NullIF())

[nahpets]

Hallo,

so sollte es gehen:

SQL-Code:

select
  a.menge as bestand,
  b.menge as reserviert,
  (IsNull(a.menge,0) - IsNull(b.menge,0)) as diff
from tab1 a left
join tab2 b on (a.name = b.name)

[jangbu]

super Danke!

jangbu

[joachimd]

Zitat:

Zitat von jangbu

richtig wäre aber:
menge - NULL = menge

Das wäre vielleicht das von Dir gewünschte, nicht aber das richtige Ergebnis. NULL heißt nicht 0, sondern undefiniert und daher MUSS jede Operation, die mit einem NULL-Wert rechnet, ebenso NULL zurückliefern. Klingt komisch, ist aber so;)

Zum Vergleich: Unendlich+1 ist immer noch Unendlich

[nahpets]

Hallo Joachim,

im Prinzip dürftes Du hier recht haben. Im aktuellen Beispiel sieht mir das aber eher so aus:

Wir haben eine 1:1-Beziehung, bei der man (aus Bequemlichkeit?) auf die 1 hinter dem : verzichtet, sofern dort nur 0 als Wert vorhanden ist. Es dürfte hier wohl korrekter sein, diesen Satz auch dann anzulegen, wenn b.menge = 0 ist.

Für meine Begriffe eine häufig verwendete Unsitte, die die Abfragen unnütz kompilziert machen. Bei "ordentlicher" Ausmodellierung und konsequenter Umsetzung dürfte so manch ein Left Join überflüssig sein und sich oben vorgeschlagene Konstrukte erübrigen.

[jangbu]

Hab jetzt aber doch noch mal eine Frage dazu:

die Tabelle bestand (a) ist 1:n mit der Tabelle reserviert (b) verknüpft. Es kann also für einen Artikel in (a) mehrer reservierungen in (b) geben.
Für den Fall einer 1:1 Beziehnung klappt folgende Abfrage (auch wenn überhauptkeine Reservierungen bestehen)

SQL-Code:

select
  a.menge as bestand,
  b.menge as reserviert,
  (IsNull(a.menge,0) - IsNull(b.menge,0)) as diff
from tab1 a left
join tab2 b on (a.name = b.name)

Wie bekomme ich jedoch die Differenz - also den verfügbaren Bestand - heraus, wenn es mehrere reservierungen gibt, ich also die "Summe der reservierungen" vom Bestand abziehen muss?
Durch den left join bekomme ich soviele Zeilen zurück wie es reservierungen gibt, brauche aber nur eine, müsste vorher in der rechten tabelle die summe bilden.

jangbu

[edit=mkinzler]SQL-Tag eingefügt Mfg, mkinzler[/edit]

[p80286]

Hallo jangbu,

Zitat:

...reservierungen gibt, brauche aber nur eine, müsste vorher in der rechten tabelle die summe bilden.

so könnte mann es machen. Wenn ich mich richtig erinnere hast Du beim M$-SQL-Server die Möglichkeit eine temporäre Tabelle anzulegen, die würde ich dann für die Abfage nutzen. Eine andere Möglichkeit wäre ein entsprechender View oder ggf. eine Funktion die Dir die Daten zurückgibt.
In meiner Erinnerung sind die MS-Views aber elend langsam.

warum gibt es eigentlich mehrere einträge mit Reservierung?

Gruß
K-H

[nahpets]

Hallo,

mal ein nicht getesteter Versuch:

SQL-Code:

select 
  Bestand,
  Reserviert,
  Bestand - Reserviert As Diff
from
(
  select
    a.Name,
    Max(IsNull(a.Menge,0)) As Bestand, /* Sollte immer der gleiche Wert sein. */
    Sum(IsNull(b.Menge,0)) As Reserviert
  from tab1 a left 
  join tab2 b on (a.Name = b.Name)
  Group By a.Name
) intern

[joachimd]

müsste auch über eine einfache Gruppierung gehen:

SQL-Code:

select 
  a.Bestand,
  b.Reserviert,
  a.Bestand - ifnull(b.Reserviert,0) As Diff
from
tab1 a left outer join
(
  select
    Name,
    Sum(Menge) As Reserviert  -- Aggregatfunktionen ignorieren NULL-Werte
    from tab2
    Group By 1
) b
on a.name=b.name

Je nach DBMS ist diese Variante schneller.
Übrigens: Verzichte bitte auf die Verknüpfung über den Namen und nimm einen nichtssagenden Schlüssel (Primary Key, Autoinc -bäh- oder GUID). Einmal den Namen falsch geschrieben, findest Du die richtige Menge nicht mehr.
Und noch eine ganz exotische Geschichte, die eventuell noch mehr Performance bringen kann - und vor allem auch hilft, falls Du mehrere Bestände des gleichen Artikels in Tab1 hast:

SQL-Code:

select 
  name,
  sum(bestand) as bestand, 
  sum(reserviert) as reserviert,
  sum(bestand)-sum(reserviert) as diff
from
(
  select name, bestand, 0 as reserviert from tab1
  union
  select name, 0 as bestand, reserviert from tab2
)
group by 1

[nahpets]

Hallo,

@Joachim:

Deine 2. Variante ist "nur" oracletauglich, SQL-Server frisst das nicht unverändert, aber das ist mir momentan relativ egal. Zu dieser Variante habe ich eine Verständnisfrage.

Soweit ich weiß wirft union doch doppelte Sätze heraus, hieße das hier nicht, dass wir eventuell mit "Datenverlust" rechnen müssen?

SQL-Code:

select name, bestand, 0 as reserviert from tab1 
union 
select name, 0 as bestand, reserviert from tab2

Wenn wir hier in tab 2 zufällig 2 identische Sätze haben, was ja zumindest theoretisch möglich sein könnte, ist doch nachher die Summe für Reserviert eventuell falsch. Müsste es hier nicht union all heißen. Bin mir da aber zugegeben momentan nicht sicher. Prinzipiell ist Deine Methode mit den "leeren" Spalten und dem Union mit anschließender Gruppierung hervorragend geeignet, auch komplexe Abfragen zu vereinfachen. Unter Laufzeitgesichtpunkten habe ich das bisher noch garnicht betrachtet.

Code:

-- Aggregatfunktionen ignorieren NULL-Werte

Ist diese Aussage immer und für alle Datenbanken richtig?

[joachimd]

Zitat:

Zitat von nahpets

Deine 2. Variante ist "nur" oracletauglich, SQL-Server frisst das nicht unverändert, aber das ist mir momentan relativ egal. Zu dieser Variante habe ich eine Verständnisfrage.

mea culpa...Fehler von mir...habe den Tabellen-Alias vergessen :(

SQL-Code:

select
  name,
  sum(bestand) as bestand,
  sum(reserviert) as reserviert,
  sum(bestand)-sum(reserviert) as diff
from
(
  select name, bestand, 0 as reserviert from tab1
  union
  select name, 0 as bestand, reserviert from tab2
) anyalias
group by 1

Zitat:

Soweit ich weiß wirft union doch doppelte Sätze heraus, hieße das hier nicht, dass wir eventuell mit "Datenverlust" rechnen müssen?

noch'n kleiner Fehler...natürlich UNION ALL (behält die Dubletten)

Zitat:

Code:

-- Aggregatfunktionen ignorieren NULL-Werte

Ist diese Aussage immer und für alle Datenbanken richtig?

IIRC ist dies ANSI-Standard
--
puhhh...ich sollte doch die Beispiele kurz nachvollziehen und nicht einfach drauflostippen

[mkinzler]

Zitat:

Ist diese Aussage immer und für alle Datenbanken richtig?

Darauf würde ich mich nicht verlassen

[nahpets]

Hallo,

Zitat:

Zitat von mkinzler

Zitat:

Ist diese Aussage immer und für alle Datenbanken richtig?

Darauf würde ich mich nicht verlassen

Joachim schreibt ja auch extra, dass es Standard ist. D. h. ja nicht, dass bei irgendeiner Datenbank der Standard zu 100% umgesetzt ist.
[OT]Wir hatten hier bei uns letztens ein paar Herren von einem Datenbankhersteller, die uns stolz mitteilten, dass Ihre Datenbank den Standard zu 92% einhält. Die Frage, was denn genau die übrigen 8% seien, so dass man sich als Entwickler darauf einstellen kann, konnte leider nicht beantwortet werden. ;-) Aber die Gesichter waren Gold wert. 8)[/OT]