Cross Join

Grundidee von relationalen Datenbanken ist die Betrachtung von Datenbanken als Mengen und Operationen, wie sie durch die mathematische Mengenlehre definiert sind.

Gegeben sei z.B. eine Menge von Himmelskörpern (Sonne, Jupiter, Erdmond, Ceres, …) und eine Menge von Gewichtsangaben der Himmelskörper, dargestellt in Vielfachen der Erdmasse (1 [=Erde], 0,0123 [=Erdmond], 332981 [=Sonne], …). Auf diesen beiden Mengen kann das sog. Kreuzprodukt gebildetet werden, indem jedes Element der einen Menge mit jedem Element der anderen Menge kombiniert wird. Hat z.B. die Menge der Himmelskörper n Elemente, und die Menge der Erdmassen m Elemente, dann hat das Ergebnis des Kreuzproduktes m * n Elemente.

  |Himmelskörper| == n, |Erdmassen| == m => |Himmelskörper| x |Erdmassen| == m*n

In der Relationalen Datenbank werden Mengen durch Tabellen dargestellt. Das Kreuzprodukt entsteht durch eine spezielle Select-Anweisung, in deren From- Klausel entweder mehrere Tabellen durch den Komma, oder durch den CROSS JOIN Operator kombiniert werden:

use master
go
drop database u3Joins
go
create database u3Joins
go
use u3Joins
go
create table HK (
        Name varchar(255)
)
insert into HK values('Sonne')
insert into HK values('Mond')
create table EM (
        Masse float
)
insert into EM values(332981)
insert into EM values(1)
insert into EM values(0.0123)
go
-- Natürlicher Join (math.: Kreuzprodukt) 
select Name, Masse
 from HK, EM
-- Alternativ
select Name, Masse
from HK CROSS JOIN EM

Inner Join

Bedingt durch Normalisierung bei der Datenmodellierung verteilen sich die Informationen zu Entities auf viele Tabellen. Um alle Daten eines Entity wieder in einem Stück zu bekommen, ist ein verknüpfen der Tabellen nötig (Inner Join).

Das Prinzip der Verknüpfung aus mengentheoretischer Sicht besteht im bilden des Kreuzproduktes zwischen den durch zwei Tabellen dargestellten Mengen, und dem anschließenden Herausfiltern all jener Tupel, bei denen Schlüssel und Fremdschlüssel übereinstimmen. Folgendes Bild demonstriert das Verfahren. Schlüssel und Fremdschlüssel werden dabei durch die astrologischen Symbole der Himmelskörper ausgedrückt.

Folgende Übung erklärt schrittweise den Inner Join.

  create table HK (
		ID int  primary key,
        Name varchar(255)
)
insert into HK values(1, 'Sonne')
insert into HK values(3, 'Mond')
create table EM (
		ID int identity,
		HK_ID int foreign key references HK(ID),
        Masse float
)
insert into EM (HK_ID, Masse) values(1, 332981)
insert into EM (HK_ID, Masse) values(3, 0.0123)
-- Um einen Datensatz für die Erde, zu der es keinen Masterdatensatz in HK gibt,
-- einzufügen, müssen kurzfristig Einschränkungen abgeschaltet werden
alter table EM nocheck constraint all
insert into EM (HK_ID, Masse) values(2, 1)
alter table EM check constraint all
go
-- Inner Join (math.: Einschränkung des Kreuzprodukts durch ein Filterkriterium => Relation) 
select 'inner Join', Name, Masse
from HK inner join EM on HK.ID = EM.HK_ID
-- Alternativ
select 'alternativ mit ,', Name, Masse
from HK, EM
where HK.ID = EM.HK_ID
-- Inner Joins sind Kommutativ
select 'kommutativ!', Name, Masse
from EM inner join HK on HK.ID = EM.HK_ID

Outer Join

  -- Outer Joins
-- sind nicht Kommutativ !
select 'left outer', Name, Masse
from HK left outer join EM on HK.ID = EM.HK_ID
select 'right outer', Name, Masse
from HK right outer join EM on HK.ID = EM.HK_ID

Cross Apply

Beim Join einer Tabelle mit einer Tabellenwertfuktion können als Parameter der Tabellenwertfunktion nur Konstanten oder TSQL- Variablen eingesetzt werden. Folgender Join liefert nur die Trabanten der Sonne:

  Select H.Name as [Zentralkörper],
	   T.ZentralkoerperTyp as [Typ],
	   H.Masse_in_kg as Masse, 
	   T.Trabant as Trabant,
	   T.Umlaufdauer_Tage,
	   T.TrabantTyp as [Typ Trabant]
from [dbo].[HimmelskoerperTab] as H Join dbo.Trabanten_von('Sonne') as T on H.ID = T.ZentralID
order by Masse desc, Umlaufdauer_Tage

Mittels des CROSS APPLY Operators kann diese Einschränkung überwunden werden. Parameter können hier an Attribute von Datensätzen gebunden werden, welcher der linke Teil von CROSS APPLY liefert. Folgender CROSS APPLY liefert die Trabanten der Sonne, der Planeten usw.

  
Select H.Name as [Zentralkörper],
	   T.ZentralkoerperTyp as [Typ],
	   H.Masse_in_kg / dbo.Erdmasse() as Masse, 
	   T.Trabant as Trabant,
	   T.Umlaufdauer_Tage,
	   T.TrabantTyp as [Typ Trabant],
	   T.Trabantmasse / dbo.Erdmasse() as Trabantmasse
from [dbo].[HimmelskoerperTab] as H CROSS APPLY dbo.Trabanten_von(H.Name) as T
order by Masse desc, Umlaufdauer_Tage 

Select ... into <Tabellenname>...

Mittels Select Into werden neue Tabellen erstellt.

Select über Datenbankgrenzen

Die From- Klausel einer Select Anweisung kann so formuliert werden, daß auf Tabellen von Datenbanken außerhalb des aktuellen Datenbankkontextes (use DBName) erreichbar sind. Dazu ist der Datenbankname dem Besitzernamen voranzustellen:

  use dms
go
select name from dms2.dbo.dirs  -- Zugreifen auf die Dirs- Tabelle in dms2
go

Select über Servergrenzen

Auch über Servergrenzen hinaus sind Selects möglich. Dazu müssen die fernen Server zuerst als Verbindungsserver definiert werden. Dies geschieht mit der gespeicherten Prozedur sp_addlinkedserver.

exec sp_addlinkedserver 'shuttle\msde2'

Nach dieser Registrierung sind auch Datenbankobjekte aus anderen Servern erreichbar, indem der Servernamen dem Namen des Datenbankobjekts vorangestellt wird:

  use dms
go
select * from [shuttle\msde2].dms.dbo.dirs
go

Select ... UNION

Durch UNION können die Ergebnisse mehrerer Select- Abfragen zu einem Resultset zusammengefasst werden. Dabei müssen alle Teilselects die gleiche Spaltenliste zurückgeben. Beispiel:

  -- UNION
-- Bestimmen aller Dateien, die das Wort 'perl' enthalten. Die Daten sind 
-- auf zwei Serverinstanzen verteilt (local und trAC19\MSDE). Durch Union werden
-- beide Teilergebnisse wieder zusammengeführt
select file_id, pos 
from dms.dbo.words
where word like 'perl'
UNION
select file_id + 100000, pos 
from [trAC19\MSDE2].dms.dbo.words
where word like 'perl'
order by file_id, pos
go