SQL join mit nur letztem Satz

[Peet]

Hallo zusammen,
ich habe mal wieder eine Frage zu SQL...:

Ich möchte 2 PF's, Kundestamm und Auftragsdatei, in einem Join verknüpfen.
Kundenstamm ist die führende Datei, Verhältnis zur Auftragsdatei natürlich 1:n.

select a.*, b.*
from kunde a
left outer join (oder nur join, ist egal) auftrag b
on b.aufkdnr = a.kdskdnr

Dabei möchte ich aber nur die Verknüpfung mit dem neuesten Satz aus der Auftragsdatei,
kann ich über Zeitstempel erkennen.

Kann mir jemand auf die Sprünge helfen ???
Vielen Dank im Voraus.
Vg.
Peet

[dschroeder]

Ich würde eine Common Table Expression verwenden, die mir zuerst die Aufträge mit dem jeweils neuesten Zeitstempel liefert. Dann würde ich darauf joinen:

Ungefähr so:

with neuesteAuftraege as (
select aufkdnr, max(zeitstempel) as newestTS
group by aufkdnr)

select * from kunde
join neuesteAuftraege on aufkdnr = kdnr and zeitsempel = newestTS

[Fuerchau]

Lateral scheint immer noch unbekannt zu sein:

select a.*, b.*
from kunde a 
left join lateral  (
   select * from auftrag b
   where b.aufkdnr = a.kdskdnr
   order by zeitstempel desc 
   limit 1
) b on 1 = 1

Empfehlenswert ist ein Index über Kunde und Zeitstempel.

[Peet]

Vielen Dank an euch beide !

lateral war mir tatsächlich nicht bekannt, die Lösung von dschroeder in der Form auch noch nicht.
Vg.
Peet

[KingofKning]

Hier mal die Antwort von der kostenfreien Chatgpt.

Du:

Ich möchte 2 PF's, Kundestamm und Auftragsdatei, in einem Join verknüpfen. Kundenstamm ist die führende Datei, Verhältnis zur Auftragsdatei natürlich 1:n. select a.*, b.* from kunde a left outer join (oder nur join, ist egal) auftrag b on b.aufkdnr = a.kdskdnr Dabei möchte ich aber nur die Verknüpfung mit dem neuesten Satz aus der Auftragsdatei, kann ich über Zeitstempel erkennen.

ChatGPT:

Um in einem SQL-Join nur den neuesten Satz aus der Auftragsdatei (1:n-Verhältnis) pro Kunde zu bekommen, kannst du ein Unter-Select oder ein CTE (Common Table Expression) verwenden, um pro Kunde den Auftrag mit dem neuesten Zeitstempel zu ermitteln.
Hier ist eine Lösung, die in vielen SQL-Dialekten (inkl. IBM DB2/AS/400 SQL) funktioniert:

---

? Variante mit LEFT JOIN und SELECT MAX(zeitstempel):




SELECT a.*, b.*
FROM kunde a
LEFT JOIN (
SELECT x.*
FROM auftrag x
INNER JOIN (
SELECT aufkdnr, MAX(zeitstempel) AS maxzeit
FROM auftrag
GROUP BY aufkdnr
) y ON x.aufkdnr = y.aufkdnr AND x.zeitstempel = y.maxzeit
) b ON a.kdskdnr = b.aufkdnr


---

???? Erklärung:

• x ist die komplette Auftragsdatei.
• In der Unterabfrage y ermittelst du je Kunde (aufkdnr) den maximalen Zeitstempel.
• Dann verbindest du x mit y, um nur den Auftrag mit dem neuesten Zeitstempel pro Kunde zu bekommen.
• Danach machst du einen Join mit der Kundenstammtabelle a.

---

? Hinweise:

• Achte auf Datentyp-Kompatibilität bei zeitstempel (z.?B. DATE, TIMESTAMP, CHAR).
• Falls mehrere Aufträge den exakt gleichen Zeitstempel haben, werden alle zurückgegeben.
• Du kannst LEFT JOIN verwenden, damit auch Kunden ohne Auftrag angezeigt werden.

---

Wenn du auf einer AS/400 (iSeries, Db2 for i) arbeitest und lieber mit RANK() oder ROW_NUMBER() arbeitest (ab V7R1+), kann ich dir auch diese Variante zeigen.
Möchtest du das lieber mit ROW_NUMBER()?

Du:

Hast Du auch eine Lösung mit Lateral?

ChatGPT:



Nachgedacht für 34 Sekunden




Lösung mit?LATERAL?(“CROSS/LEFT?JOIN?LATERAL”)

Ab IBM?i?7.1 (bzw. Db2?for?i 7.1) steht Ihnen der LATERAL?Join zur Verfügung. Er erlaubt es, in einer abgeleiteten Tabelle (Subselect) auf Spalten der zuvor genannten Tabellen zuzugreifen?–?vergleichbar mit CROSS APPLY in SQL?Server. Dadurch können Sie in einem einzigen Schritt pro Kunde genau den jüngsten Auftrag ziehen.



SELECT a.*, -- alle Felder aus dem Kundenstamm
b.* -- plus Felder des jüngsten Auftrags
FROM kunde a
LEFT JOIN LATERAL -- oder CROSS JOIN LATERAL = INNER?Verknüpfung
( SELECT b.* -- Unterabfrage = “pro Kunde Aufträge”
FROM auftrag b
WHERE b.aufkdnr = a.kdskdnr -- korreliert über Kundennummer
ORDER BY b.zeitstempel DESC -- jüngster zuerst
FETCH FIRST 1 ROW ONLY -- genau EIN Datensatz
) b
ON 1 = 1 ; -- Dummy?ON (bei LEFT JOIN LATERAL erforderlich)


Variante	Wirkung
LEFT JOIN LATERAL … ON 1=1	Alle Kunden bleiben erhalten; bei fehlenden Aufträgen sind die Spalten aus?b?NULL.
CROSS JOIN LATERAL …	Entspricht einem?INNER?Join: Nur Kunden mit mindestens einem Auftrag werden gezeigt.
FETCH FIRST 1 ROW WITH TIES	Liefert alle Aufträge, falls mehrere den exakt gleichen Zeitstempel haben.

Warum das funktioniert

• LATERAL sorgt dafür, dass die Unterabfrage für jede Zeile von?kunde separat ausgeführt wird – also „row?by?row“.?
  RPGPGM.COM - From AS400 to IBM i
  
• Durch das ORDER BY … FETCH FIRST 1 ROW ONLY wird in der Unterabfrage genau der neueste Auftrag des jeweiligen Kunden ausgewählt, bevor die Daten wieder nach außen gereicht werden.?
  BlogFaq400
  Stack Overflow

Performance?Hinweise

1. Index auf?auftrag(aufkdnr, zeitstempel DESC) beschleunigt die Unterabfrage enorm.
2. Bei sehr breiten Tabellen können Sie statt b.* nur die benötigten Spalten auswählen.
3. Wenn Sie weitere Kriterien (Status, Jahr?usw.) haben, setzen Sie sie einfach zusätzlich in die Unterabfrage.

Damit haben Sie eine elegante, kompakte Alternative zu den klassischen Lösungen mit GROUP?BY?+?MAX oder OLAP?Funktionen – lesbarer und häufig sogar schneller.

pacity-30 w-9 bg-[#00000014] dark:bg-[#FFFFFF29] text-token-text-primary hover:bg-[#0000001F] hover:dark:bg-[#FFFFFF3D]" style="border: 0px solid rgba(13, 13, 13, 0.05); box-sizing: border-box; margin: 0px; padding: 0px; background-color: rgba(0, 0, 0, 0.08); border-radius: 3.40282e+38px; color: rgb(13, 13, 13); font: inherit; letter-spacing: inherit; opacity: 1; appearance: button; cursor: pointer; position: relative; display: flex; height: 36px; width: 36px; align-items: center; justify-content: center; transition-duration: 0.15s; transition-property: color, background-color, border-color, outline-color, text-decoration-color, fill, stroke, --tw-gradient-from, --tw-gradient-via, --tw-gradient-to; transition-timing-function: cubic-bezier(0.4, 0, 0.2, 1); view-transition-name: none;">

ChatGPT kann Fehler machen. Überprüfe wichtige Informationen. Siehe Cookie-Voreinstellungen.

[B.Hauser]

Mir ist nachwie vor nicht klar warum ihr unbedingt LATERAL verwenden wollt!
Die beste Lösung (i.Ü. auch die performanteste) ist die Lösung von Dieter, d.h. die Verwendung von Common Table Expressions.

Die Lösung mit LATERAL ist ungeführt 3x langsamger wie die Lösung mit CTE. Sagt zumindest Visual Explain, wenn man beide Syntaxen durchlaufen lässt. Ich habe leider in meinen Spiel-Dateien nicht genug Daten, so dass man den Unterschied tatsächlich merken könnte.

... das behauptet auch ChatGPT:
Du liegst mit deiner Strategie (CTEs > Subselects/OLAP > LATERAL/Skalare) vollkommen richtig – besonders in einem System, das du verstehen, testen und langfristig warten möchtest.

[KingofKning]

Mir persönlich ging es eigentlich nur darum zu zeigen wie gut die Antworten auf die gestellten Fragen waren.
Ich bin von beiden Antworten positiv überrascht und hätte nicht vermutet das eine Software die in meinen Augen gute Lösung in sekundenschnelle liefert.

Wenn ich noch Programmierer wäre bekäme ich langsam Angst um meinen Job.

GG 2139

[Peet]

Mir persönlich ging es eigentlich nur darum zu zeigen wie gut die Antworten auf die gestellten Fragen waren.
Ich bin von beiden Antworten positiv überrascht und hätte nicht vermutet das eine Software die in meinen Augen gute Lösung in sekundenschnelle liefert.

Wenn ich noch Programmierer wäre bekäme ich langsam Angst um meinen Job.

GG 2139

Vielen Dank.
Es freut micht dass du mit ChatGtp in so kurzer Zeit ein Ergebnis erzielt hast.
vg.
Peet

[Peet]

Mir ist nachwie vor nicht klar warum ihr unbedingt LATERAL verwenden wollt!
Die beste Lösung (i.Ü. auch die performanteste) ist die Lösung von Dieter, d.h. die Verwendung von Common Table Expressions.

Die Lösung mit LATERAL ist ungeführt 3x langsamger wie die Lösung mit CTE. Sagt zumindest Visual Explain, wenn man beide Syntaxen durchlaufen lässt. Ich habe leider in meinen Spiel-Dateien nicht genug Daten, so dass man den Unterschied tatsächlich merken könnte.

... das behauptet auch ChatGPT:
Du liegst mit deiner Strategie (CTEs > Subselects/OLAP > LATERAL/Skalare) vollkommen richtig – besonders in einem System, das du verstehen, testen und langfristig warten möchtest.

Hallo Birgita,
vielen Dank für die Infos.
Vg.
Peet

[Fuerchau]

@Birgittta
Meine Erfahrung spricht da vollkommen dagegen.
Lateral Table ist die bisher schnellste Möglichkeit, wie ich festgestellt habe, z.B. per where ... Order By ... Limit n, Sätze zu joinen oder eben auch einen "select Aggregates from .. where .." ohne aufwändigen Group by zu verknüpfen.
Der Lateral ist letztlich genauso schnell wie die "Aggr() Over()"-Funktionen, die man früher auch mit CTE's o.ä. lösen musste. Und dagegen hast du ja auch nichts.

Per CTE oder auch derived Table einen Grouped Join durchzuführen ist merklich langsamer.
Dasselbe gilt auch für skalare Subselects, die damit vollkommen obsolet werden, zumal man mit den Ergebnissen des Laterals auch gleich weiterrechnen kann.
Vielleicht probiert du ihn einfach auch mal aus. Vor allem auf Tabellen mit ein paar 100.000 oder mehr Zeilen. Auf Testumgebungen mit ein paar 1000 Zeilen macht das natürlich kaum einen Unterschied.

Also rede den Leuten nicht ein, dass Lateral Unsinn sei. Es ist einer der effektivsten Joins überhaupt und so komplizierte CTE's wie oben angegeben sind nicht mehr erforderlich. Ich verwende den für fast alle Belange, die früher ungleich komplizierter waren.

[BenderD]

... imho sind ANSI konforme Konstrukte vorzuziehen und die einfachste, am besten lesbare Variante ist am besten. Wenn dann eine Variante langsamer ist, taugt der Optimizer nichts. Am Rande sei noch vermerkt, dass komplizierte SQL Statements auch ein Zeichen für lausiges Datenbankdesign sein könnten.

Der Griff nach KI ist meist ein Zeichen von Mangel an NI oder Faulheit - kann aber nach umgekehrtem Eiffelturm-Prinzip durchaus Karriere-fördernd sein.

D*B

[Fuerchau]

Ich habe mir nun mal die Mühe gemacht, den Explain mi den SQL's zu prüfen:

left join Lateral mit where order by limit
führt zu
Index Probe => Table Probe => Fetch n Rows => nested Loop Join

left join CTE mit On-Klausel führt zu
Index Probe => Table Probe => Aggregation => nested Loop

Der Unterschied ist, dass nach dem Lateral nur noch ein Fetch einer Zeile passiert, während bei dem Grouped-Join noch ein Aggregat gebildet werden muss, was durchaus mehr als 1 Zeile umfassen kann.

Gegen den CTE spricht ebenso, dass ich einen 2. Left join mit dem CTE benötige, wenn ich weitere Werte aus dem Satz haben muss.
Das funktioniert halt nicht mit dem CTE, da ich hier einen Group By benötige um Aggregate verwenden zu können.
Dies führt dann zu einem 2. Index Probe => Table Probe => nested Loop

Wie du an meinem Lateral siehst, benötige ich nur ein b.* um aus dem Lateral alles zu erhalten, was ich benötige.

Ein weiteres Argument gegen CTE's ist leider auch, dass ich diese nicht in einem Insert oder Merge verwenden kann, sondern hier auf derived Table gehen muss.

Es gibt aber noch eine weitere Anwendung für lateral.
Wer kennt nicht die Tabellen mit pseudo Array-Feldern, die durchnummeriert sind.
Bisher musste man diese mit einem etwas kompexeren union all trennen um sie per SQL vernünftig zu verarbeiten:

select artikel, menge01 as menge from myfile
union all
select artikel, menge02 as menge from myfile
:

Dies führt dann zu n * Indexscan => Table Probe => union all

Mit Lateral ist das nun erheblich einfacher:

select artikel, x.* from myfile
cross join lateral (
  values(menge01)
   union all 
  values(menge02)
) x (menge)

Dies führt zu n * values list => union all => nested loop

Außerdem gehört Lateral bei vielen anderen DBM's inzwischen zum Standard.

Wer mehr wissen will:
https://medium.com/@goldengrisha/a-d...n-7b09fcb3b745