Selfjoins

Was ist ein Self-Join?

Ein ''Self-Join'' liegt dann vor, wenn eine Tabelle in der FROM-Klausel einer SELECT-Anweisung mehrfach auftritt. Damit die einzelnen Exemplare der Tabelle in der FROM-Klausel voneinander unterschieden werden können, werden ihnen Aliasnamen angefügt. Eine spezielle Form des Self-Join ist eine Verknüpfung einer Tabelle mit sich selbst. Häufig kommt aber auch eine Verknüpfung eines einzelnen Feldes der mehrfach auftretenden Tabelle mit verschiedenen Feldern einer anderen Tabelle vor.

Abstrakt kann man sich diese Situation so vorstellen (siehe Beispiele):

Gegeben sei eine Tabelle tab_a mit den Feldern

Dabei sind die Felder ex1 und ex2 INTEGER-Felder, die sich auf ein SERIAL-Feld „nr“ in der Tabelle tab_b beziehen:

Das bedeutet: in der Tabelle tab_a werden zwei verschiedene Bezüge auf das Feld Nr der Tabelle tab_b gespeichert. Will man jetzt z.B. eine Ausgabe von Tabelle tab_a machen und dabei die korrespondierenden Namen zu den Nummern ex1 und ex2 aus der Tabelle tab_b jeweils mit anzeigen, so muss man in der Tat einen Self-Join auf Tabelle tab_b machen:

SELECT tab_a.nr, tab_a.name, b1.name, b2.name 
   FROM tab_a, tab_b b1, tab_b b2 
   WHERE tab_a.ex1 = b1.nr and tab_a.ex2 = b2.nr 

Beispiele

Ausgabe von Views und ihren Abhängigkeiten

Tabelle sysviews

Da ein View auf einem bereits existierenden View basieren kann, gibt es eine weitere Tabelle sysdepend, in der die Abhängigkeiten der Views untereinander dokumentiert sind:

Tabelle sysdepend

Wenn also ein View v2 auf einem View v1 und einer Tabelle t1 basiert, findet man folgende Einträge in der Tabelle sysdepend:

Es soll eine Liste aller View-Abhängigkeiten in der Datenbank ausgegeben werden,
und zwar mit folgenden Feldern:

View-Name oder Tabellenname
Name des abhängigen Views

Lösung:

SELECT st1.tabname, st1.tabname 
 
   FROM systables st1, systables st2, 
      sysdepend sd 
   WHERE st1.tabid = sd.btabid and st2.tabid=dtabid

Wer hält gerade welche Locks auf Datenbankobjekte?

select 
   stn.dbsname[1,10] dbsname, 
   stn.tabname[1,10] tabname, 
   trim (su2.us_name) || "/" || su2.us_sid locker_sid, 
   count(*) anzahl_wartend 
   from sysuserthreads su, syslocktab sl, sysuserthreads su2, 
      systabnames stn 
   where su.us_lkwait = sl.lk_addr
      and 
      su2.us_txp = sl.lk_owner 
      and 
      stn.partnum = sl.lk_partnum 
      and stn.dbsname = $DBNAME 
   group by 1,2,3 
   into temp h2;
unload to warter_anzahl 
select dbsname, tabname, locker_sid[1,20], anzahl_wartend 
   from h2; 
 
 

Die eigentlich wichtige Information – wer welchen Lock hält – steckt hier in der Tabelle syslocktab. Aber diese enthält zwei verschiedene Adressen: nämlich sl.lk_addr
als Adresse des ersten Waiters, also des auf diese Sperre wartenden Benutzers, und die Adresse sl.lk_owner, welches die Adresse des Eigentümers der Sperre ist. Genauere Informationen über diese Akteure auf der Transaktions-Bühne findet man in der Tabelle sysuserthreads , aber jetzt wird der Join jeweils auf andere Felder vollzogen:

Nämlich

su.us_lkwait = sl.lk_addr 
      und 
su2.us_txp = sl.lk_owner. 

Auch hier hat man keine andere Chance als einen Self-Join auf die Tabelle sysuserthreads zu machen, weil man aus zwei verschiedenen Zeilen dieser Tabelle Informationen braucht, die aber zu genau einer Zeile der syslocktab-Tabelle gehören!

Beispiel für einen direkten Self-Join (Hüttenwerk Krupp-Mannesmann Duisburg)

In diesem Werk werden täglich gewisse Mengen Rohstahl geschmolzen. Zu jeder Schmelze gibt es
in einer Informix-Datenbank einen Tabelleneintrag:

Master-Tabelle epaj:

 
epaj0001             decimal(1)       Schmelznummer                              
epaj0002             char(6)                                  
epaj0010             char(6)                    Phase                                 
epaj0020             char(6)                            
epaj0030             date         Abstichdatum                                  
epaj0040             char(6)                                
epaj005b             decimal(9)                          
epaj0060             char(12)                   Qualität 
epaj0070             char(5)                                 
epaj0080             char(2)                                 
epaj0090             char(2)                                 
epaj0095             char(2)                                 
epaj0110             decimal(5)                           
epaj0120             decimal(5)  
epaj0192             char(5)         Grundgüten 
epaj0194             char(12)          Grundqualität    
 

und weitere, nicht benötigte Felder.

Die Detail-Tabelle eqbf enthält pro Schmelze, Phase und Stoff einen Satz:

eqbf.epaj0010      Schmelznummer 
eqbf.epaj0030      Abstichdatum 
eqbf.eqbf0120n      Stoff  (z.B. „999“ oder „981“) 

Die Joinfelder zwischen den beiden Tabellen sind:

   epaj.epaj0010 = eqbf.epaj0010   
   epaj.epaj0030 = eqbf.epaj0030 

Nun kommt die Aufgabe:

Zeigen Sie alle Schmelzen , ihre Einsaetze, ihre Erzeugungen und Ausbringungen vom 10.12.1999.

Dabei ist die Schmelzausbringung = Menge mit dem Schluessel "981" / Menge mit Schluessel "999"

select e2.epaj0010 snr, e2.epaj0030 dat,  
   e1.eqbf0120n erzeug, 
   e2.eqbf0120n einsatz, 
   100 * e1.eqbf0120n / e2.eqbf0120n  
   from eqbf e1, eqbf e2 
   where e2.epaj0030 = "10121999" 
      and e1.epaj0010 = e2.epaj0010 
      and e1.epaj0030 = e2.epaj0030 
      and e1.eqbf0130 = "981" 
      and e2.eqbf0130 = "999"