Montag, 6. November 2017

Verwenden des APPLY Operators - CROSS und OUTER APPLY in T-SQL


Der Apply Operator ermöglicht es eine Tabelle und eine Tabellenwertfunktion miteinander zu verknüpfen. Ähnlich einem JOIN.

Beispiel:
SELECT * FROM Mitarbeiter m 
CROSS APPLY dbo.GibGehaltZurueck(m.Gehaltsklasse) 

Mittels CROSS Apply können Spaltenwerte der Tabelle zur linken an die parameterisierte Tabellenwertfunktion auf der rechten Seite vergeben übergeben werden.

Aber sehen wir uns das folgende Beispiel an:


-- Anlegen der Beispieltabellen Mitarbeiter und Gehalt sowie der Tabellenfunktion (gibt eine -- Tabelle zurück)  GibGehaltZurueck


-- START TABELLEN U. FUNKTION ERZEUGEN HIER 
USE [tempdb] 
GO

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Gehalt]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [Gehalt] 
END 
CREATE TABLE [Gehalt]( 
   [Gehaltsklasse] nvarchar(100) NOT NULL PRIMARY KEY, 
   [Gehalt] money NOT NULL, 



insert into Gehalt (Gehaltsklasse,Gehalt) values('A',2700),('B',7400),('C',2590),('D', 4500);

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Mitarbeiter]') AND type IN (N'U')) 
BEGIN 
   DROP TABLE [Mitarbeiter] 
END 
GO 

CREATE TABLE [Mitarbeiter]( 
   [Mitarbeiter_id] [int] NOT NULL PRIMARY KEY, 
   [Vorname] nvarchar(100) NOT NULL, 
   [Nachname] nvarchar(100) NOT NULL, 
   [Gehaltsklasse] nvarchar(100) NOT NULL , 
) ON [PRIMARY] 
GO

insert into mitarbeiter (Mitarbeiter_id,vorname,nachname,gehaltsklasse) values
(1,'Hanna','Fredericks','A'),(2,'Mike','Franke','B'),(3,'Achim','Gregorios','A'),(4,'Holger','Jolandos','Y'),
(5,'Richie','Retorius','X'),(6,'Anna','Kastner','A'),(7,'Katrin','Lemmes','C'),(8,'Michael','Maier','C')


-- anlegen einer tabellenwertfunktion

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[GibGehaltZurueck]') AND type IN (N'IF')) 
BEGIN 
   DROP FUNCTION dbo.GibGehaltZurueck 
END 
GO 
CREATE FUNCTION dbo.GibGehaltZurueck(@gehaltsklasse AS nvarchar(100))  
RETURNS TABLE 
AS 
RETURN 
   ( 
   SELECT * FROM gehalt where gehaltsklasse = @gehaltsklasse   
   ) 
GO 

-- END TABELLEN U. FUNKTION ERZEUGEN HIER 


Im folgenden werden die Tabellen aufgelistet um die Inhalte zu verstehen. Die Tabelle Mitarbeiter enthält die jeweiligen Mitarbeiter mit einer Mitarbeiterid, Vornamen und Nachnamen sowie einer zugeordneten Gehaltsklasse. In der Tabelle Gehalt, befinden sich die Datensätze, die das Gehalt das der jeweiligen Gehaltsklasse zugeordnet ist repräsentieren. Also alle Mitarbeiter der Gehaltsklasse A haben demnach ein Gehalt von 2700,--.

Die Tabellenwertfunktion GibGehaltZurueck ermittelt den Gehaltsbetrag entsprechend der im Parameter der Funktion übergebenen Gehaltsklasse.

-- START Auflistung der Tabellen u. Einsatz der Funktion GibGehaltZurueck
select * from Mitarbeiter
select * from Gehalt
select * from dbo.GibGehaltZurueck('A')
-- END Auflistung der Tabellen u. Einsatz der Funktion GibGehaltZurueck


Um das Gehalt eines Mitarbeiters festzustellen bedarf es keiner Funktion da eine normale Verjoinung der Tabellen Mitarbeiter und Gehalt über die Spalte Gehaltsklasse ausreichen würde.

-- START ein normaler Join ermittelt ohne weiteres das der Gehaltsklasse zugeordnete Gehalt
select vorname,nachname,gehalt.Gehaltsklasse, Gehalt 
from mitarbeiter inner join gehalt 
on (mitarbeiter.Gehaltsklasse = gehalt.Gehaltsklasse)

select vorname,nachname,gehalt.Gehaltsklasse, Gehalt 
from mitarbeiter left outer join gehalt 
on (mitarbeiter.Gehaltsklasse = gehalt.Gehaltsklasse)
-- END ein normaler Join ermittelt ohne weiteres das der Gehaltsklasse zugeordnete Gehalt


Was aber wenn eine Tabelle auf der linken Seite mit einer Funktion auf der rechten Seite verknüpft werden soll oder der Funktion Parameterwerte aus einer Spalte der Linken Tabelle übergeben werden sollen.

Beispiel1:

-- eine Funktion kann auch auf der einen Seite der Join Verknüpfung stehen ABER !!! ...
select vorname,nachname,gehalt.Gehaltsklasse, Gehalt 
from mitarbeiter m inner join dbo.GibGehaltZurueck('A') 
gehalt on (1=1)


ABER!!!

Beispiel2:
-- es kann dem Parameter der Funktion kein Spaltenwert (gehaltsklasse)
-- aus der Tabelle der linken Seite der Join Verbindung übergeben werden. FEHLER!!!

-- dieses Beispiel führt zu einem Fehler
SELECT * FROM Mitarbeiter m 
inner join dbo.GibGehaltZurueck(m.gehaltsklasse) on 1=1
GO 

DAZU WIRD CROSS APPLY oder OUTER APPLY BENÖTIGT!!!

CROSS APPLY

Im folgenden Statement wird von jedem Mitarbeiterdatensatz der linken Tabelle Mitarbeiter, der Spaltenwert Gehaltsklasse mittels m.Gehaltsklasse an den Funktionsparameter der Funktion GibGehaltZurueck auf der rechten Seite übergeben.

Liefert die Funktion gefundene Datensätze zurück werden die Mitarbeiterdatensätze inkl. der durch die Funktion zurückgegebenen Datensätze ausgegeben. Liefert die Funktion keine Datensätze entsprechend dem übergebenen Parameterwert zurück werden keine Datensätze auch nicht der Datensatz des Mitarbeiters aus der Tabelle Mitarbeiter zurückgegeben. Ähnlich wie INNER JOIN bei Verjoinung von zwei Tabellen.

OUTER APPLY

Zeigt auf jeden Fall die Datensätze der Linken Seite also die Datensätze der Tabelle Mitarbeiter an. Auch wenn die Funktion auf Grund der übergebenen Gehaltsklasse m.Gehaltsklasse keine Daten zurückliefert. Ähnlich wie LEFT OUTER JOIN bei Verjoinung von zwei Tabellen.

-- Funktioniert
SELECT * FROM Mitarbeiter m 
CROSS APPLY dbo.GibGehaltZurueck(m.Gehaltsklasse) 

-- Funktioniert
SELECT * FROM Mitarbeiter m 
OUTER APPLY dbo.GibGehaltZurueck(m.Gehaltsklasse) 


-- ENDE--

Hier der Link zum Youtube Video https://youtu.be/5VE_j6sT4EM






Keine Kommentare:

Kommentar veröffentlichen

Was ist SharePoint? Kurz erklärt für Führungskräfte.

Hier der Link zum offiziellen Youtube Video