INNER JOIN vs. FULL JOIN - funktioniert nicht richtig?

[Rosenkohl]

Hallo,

ich schreibe gerade meine Bachelorarbeit über die Implementierung eines BI-Dashboards und muss hierfür die Daten im "Data Warehouse" vorbereiten. Verwendet wird im Unternehmen die Software "Sisense", die Datenaufbereitung erfolgt dementsprechend in sogenannten ElastiCubes. Sisense verwendet den SQL-92 Standard, aber hat, meiner Meinung nach, nur sehr wenige SQL Befehle verfügbar (Beispielsweise kein ANY oder EXISTS...). Generell habe ich das Gefühl, dass dieses Programm einfach nicht richtig funktioniert. Mit "komplexeren" Abfragen kommt es überhaupt nicht klar, heißt, man muss viele kleine Schritte machen und viele Zwischentabellen anlegen, bis man das gewünschte Ergebnis bekommt.

Die Implementierung ist mittlerweile vollständig und ich bekomme das gewünschte Ergebnis. Allerdings verstehe ich nicht, warum ich über FULL JOIN das richtige Ergebnis bekomme und über INNER JOIN nicht. Ich bin mir nicht sicher, ob ich hier einen extremen Denkfehler habe, oder ob das (mal wieder) nur ein Fehler des Programms ist.

Ausgangslage sind 2 Tabellen:

Datenbank: LANR, Datum, Ziffer, Prüfzeit(in min)
CSV: LANR, Name, angemeldete Stunden, Datum Beginn, Datum Ende

Was ich erreichen möchte:
LANR, Datum und die Summe der Zeit aus der DB ausgeben + Name und angemeldete Stunden aus CSV, bei denen das Datum der Datenbank zwischen den beiden Daten der CSV liegt.

In der CSV gibt es pro LANR häufig auch nur eine angemeldete Stundenzahl, manchmal allerdings auch zwei, z.B. im Jahr 2018 780 und ab dem Jahr 2019 400.

Mein SQL Befehl ist folgender:

SELECT x.LANR, x.Date, y.Stunden, y.Name, SUM(PRUEFZEIT)/60
FROM DB x FULL JOIN CSV y
ON x.LANR = y.LANR
WHERE x.Date BETWEEN y.DatumBeginn AND y.DatumEnde
GROUP BY x.LANR, x.Date, y.Stunden, y.Name
ORDER BY x.LANR, x.Date

Dieser funktioniert auch und ich bekomme das richtige Ergebnis. Allerdings macht meiner Meinung nach dieser FULL JOIN hier überhaupt keinen Sinn, da ich keine NULL Werte habe und jede LANR aus der DB auch in der CSV vorhanden ist, auch sind alle Zeitbereiche abgedeckt.

Bei einem INNER JOIN sieht das Ergebnis allerdings anders aus. Wenn eine LANR hier zwei angemeldete Stundenzahlen hat, also eine für 2018 und eine für 2019, dann bekomme ich bei dieser LANR im Ergebnis pro Tag entsprechend zwei Reihen, mit der Stundenzahl von 2018 und mit der Stundenzahl von 2019. Es ignoriert quasi vollkommen mein WHERE statement.

Was genau ist hier das Problem? Habe ich einen Denkfehler und das kann mit einem INNER JOIN gar nicht funktionieren? Oder ist es wirklich das Programm?

Vielen Dank fürs Lesen und ich hoffe, mich kann jemand aufklären.

lg,
Julia

Edit: Lasse ich bei beiden JOIN Varianten die WHERE Bedingung weg, sind die Datensätze, die ich erhalte, gleich groß, die beiden machen hier quasi das Gleiche.

[defo2]

Ok, also dein Join Kriterium LANR ist nicht eindeutig.
Durch den JOIN ergeben sich mehrdeutige Kombinationsmöglichkeiten.
Durch die WHERE Clause werden diese "bereinigt"
Das Ganze ist noch "verwischt" durch das Group.

Mach mal folgendes:
- lasse die group funktion in Deinem Statement weg und schau was rauskommt (ohne group clause und SUM Spalte)
- ergänze nun wieder Deine WHERE Clause

Erinnere Dich an den Vorschlag von Christian und an "JOIN ist eine spezifische Form von WHERE" und nimm die Bedingung Deiner Where Clause in die JOIN Clause auf (und where natürlich weglassen)

Spiele mal mit den Daten:
ergänze eine Zeile in DB mit nicht passender LANR
lasse die Abfragen laufen (am besten ohne group)

ergänze eine Zeile in CSV mit nicht passender LANR
lasse die Abfragen laufen (am besten ohne group)

Achte darauf, dass Du mit SUM() ..Group By keine Daten addierst, die gar nicht existieren (fehlendes JOIN Kriterium)

[kuemmelchen]

FROM DB x JOIN CSV y ON x.LANR = y.LANR stellt genau einen Join zwischen beiden Tabellen dar.

Im Gegensatz dazu:
FROM DB x JOIN CSV y ON x.LANR = y.LANR WHERE x.Date BETWEEN y.DatumBegin AND y.DatumEnde

Hierbei handelt es sich um 2 Joins:
1. Join durch ON angegeben und
2. Join über BETWEEN, da x von der Tabelle DB und y von der Tabelle CSV stammen.

[Rosenkohl]

Deine Fragen sind nicht eindeutig zu beantworten ohne zumindest Beispieldaten und oder Tabellendefinition usw.
Der Full Outer Join liefert häufig mehr Zeilen, weil er "lockerer" ist.
Verwendet man im Anschluss eine Where Bedingung, die strenger ist, sind die Mehrzeilen einfach wieder futsch.
Du müsstest das eigentlich ausprobieren können, in dem Du die Where Bedingung weglässt und Ergebnisse vergleichst. Oder, falls es zu unübersichtlich ist, die Where Bedingung anpasst.

Ich hatte es im ursprünglichen Post noch ergänzt, wenn ich die WHERE Bedingung bei beiden JOINS weglasse, bekomme ich denselben Datensatz als Ergebnis, beide haben gleich viele Einträge.

Ich versuche es mal mit ein paar Beispieldaten darzustellen:

DB
LANR | Datum | Ziffer | Prüfzeit
458 | 24.03.19 | 777 | 14
458 | 24.03.19 | 757 | 8
458 | 24.03.19 | 700 | 4
458 | 24.10.18 | 777 | 14
458 | 24.10.18 | 757 | 8
400 | 24.03.19 | 777 | 14


CSV
LANR | Name | angemeldete Stunden | Datum Beginn | Datum Ende
458 | Bauer | 500 | 01.01.18 | 31.12.18
458 | Bauer | 400 | 01.01.19 | 25.03.19
400 | Müller | 350 | 01.01.18 | 25.03.19

Abfrage mit FULL oder INNER
SELECT x.LANR, x.Date, y.Stunden, y.Name, SUM(PRUEFZEIT)
FROM DB x (FULL) (INNER) JOIN CSV y
ON x.LANR = y.LANR
GROUP BY x.LANR, x.Date, y.Stunden, y.Name
ORDER BY x.LANR, x.Date

liefert beides:

LANR | Date | Stunden | Name | Zeit
458 |24.03.19 | 500 | Bauer | 26
458 |24.03.19 | 400 | Bauer | 26
458 |24.10.18 | 500 | Bauer | 22
458 |24.10.18 | 400 | Bauer | 22
400 |24.03.19 | 350 | Müller | 14

MIT WHERE x.Date BETWEEN y.DatumBeginn AND y.DatumEnde liefert der INNER JOIN nochmal dasselbe wie oben, was mir allerdings mittlerweile klar ist, warum es nicht geht.
Und der FULL JOIN liefert jetzt:

LANR | Date | Stunden | Name | Zeit
458 |24.03.19 | 400 | Bauer | 26
458 |24.10.18 | 500 | Bauer | 22
400 |24.03.19 | 350 | Müller | 14

Also genau das, was ich möchte. Warum klappt hier das mit dem WHERE ohne den Schritt über eine Zwischentabelle zu gehen, wie bei dem INNER JOIN?

[defo2]

Deine Fragen sind nicht eindeutig zu beantworten ohne zumindest Beispieldaten und oder Tabellendefinition usw.
Der Full Outer Join liefert häufig mehr Zeilen, weil er "lockerer" ist.
Verwendet man im Anschluss eine Where Bedingung, die strenger ist, sind die Mehrzeilen einfach wieder futsch.
Du müsstest das eigentlich ausprobieren können, in dem Du die Where Bedingung weglässt und Ergebnisse vergleichst. Oder, falls es zu unübersichtlich ist, die Where Bedingung anpasst.

Vielleicht hilft dir noch folgendes:
select x.x, y.y, y.x from x join y on x.x=y.x where x > irgendeine Zahl
ist gleich
select x.x, y.y, y.x from x, y where x > irgendeine Zahl on x.x=y.x

Ein Join ist (erstmal) nichts weiter als eine spezifische Where Clause. Er macht in einer Variante (INNER) das gleiche wie die entsprechende WHERE Clause.
Wenn Du Dich jetzt fragst, wie man mit einer WHERE Clause einen OUTER Join formulieren würde, dann bist Du bei einem der Gründe, warum es eben JOIN Clauses gibt.
(Je nach Hersteller können WHERE Clauses auch OUTER JOIN Varianten, aber das muss man sich heutzutage nicht mehr angewöhnen)

[Rosenkohl]

Versuch

SELECT x.LANR, x.Date, y.Stunden, y.Name, SUM(PRUEFZEIT)/60
FROM DB x JOIN CSV y
ON x.LANR = y.LANR AND x.Date BETWEEN y.DatumBeginn AND y.DatumEnde

GROUP BY x.LANR, x.Date, y.Stunden, y.Name
ORDER BY x.LANR, x.Date

Danke für den Einwand, das hilft zwar nicht weiter, aber so funktioniert der FULL JOIN immerhin richtig.

Ein INNER Join ist ungefähr das Gegenteil von einem FULL [OUTER] Join. Sinn macht der FULL OUTER join, wenn Du möchtest, dass die nicht joinbaren Sätze aus beiden Tabellen mit erscheinen - und mangels treffendem join dann die Felder der jeweils anderen Tabelle NULL "enthalten".

Genau das dachte ich mir auch, deswegen wundert es mich auch so, dass ich tortzdem mit FULL JOIN das richtige Ergebnis bekomme. Das macht überhaupt keinen Sinn.

Beim INNER Join werden nur passende Kombinationen angezeigt. Filterbedingungen der WHERE Clause müssen angewendet werden.
Dein Statement ist dort allerdings etwas "schief". Zumindest klingt es so, denn man kann es ohne Beispieldaten und Datenmodell nicht genau sagen.

Wenn Dein LANR 18 und 19 ist und es mit Datumsangaben so aussieht
18, 1.1.2018, 31.12.2018
19, 1.1.2019, 31.12.2019

Dann liefert Dein oben gezeigtes WHERE Kriterium keine zusätzlich Einschränkung zum Join Kriterium
Das würde erst der Fall sein, wenn Du z.B. Dein WHERE Kriterium selbst vorgibst:
where x.Date BETWEEN <1.1.2019> AND <31.12.2019>

Super, vielen Dank, ich verstehe wohl so langsam, wo das Problem ist. Selbst vorgeben kann ich das WHERE Kriterium leider nicht, da es ja direkt von der anderen Tabelle abhängig ist. Dann müsste ich in dem Fall zuerst einen INNER JOIN machen und dabei das Datum Beginn und Datum Ende auch angeben, und dann mit dieser Tabelle eine neue Abfrage mit der WHERE Bedingung?

Was ich allerdings trotzdem nicht verstehe: Warum liefert der FULL JOIN mit der WHERE Bedingung das richtige Ergebnis? Gilt dort nicht dasselbe?

[defo2]

Ein INNER Join ist ungefähr das Gegenteil von einem FULL [OUTER] Join. Sinn macht der FULL OUTER join, wenn Du möchtest, dass die nicht joinbaren Sätze aus beiden Tabellen mit erscheinen - und mangels treffendem join dann die Felder der jeweils anderen Tabelle NULL "enthalten".

Beim INNER Join werden nur passende Kombinationen angezeigt. Filterbedingungen der WHERE Clause müssen angewendet werden.
Dein Statement ist dort allerdings etwas "schief". Zumindest klingt es so, denn man kann es ohne Beispieldaten und Datenmodell nicht genau sagen.

Wenn Dein LANR 18 und 19 ist und es mit Datumsangaben so aussieht
18, 1.1.2018, 31.12.2018
19, 1.1.2019, 31.12.2019

Dann liefert Dein oben gezeigtes WHERE Kriterium keine zusätzlich Einschränkung zum Join Kriterium
Das würde erst der Fall sein, wenn Du z.B. Dein WHERE Kriterium selbst vorgibst:
where x.Date BETWEEN <1.1.2019> AND <31.12.2019>

[Christian Marquardt]

Versuch

SELECT x.LANR, x.Date, y.Stunden, y.Name, SUM(PRUEFZEIT)/60
FROM DB x JOIN CSV y
ON x.LANR = y.LANR AND x.Date BETWEEN y.DatumBeginn AND y.DatumEnde

GROUP BY x.LANR, x.Date, y.Stunden, y.Name
ORDER BY x.LANR, x.Date