Gegevens koppelen en bewerken in SQL met JOINs en CROSS APPLY

In SQL zijn er verschillende manieren om sets of tabellen aan elkaar te koppelen: JOINS. Hiermee koppel je één tabel (of samengestelde tabel) aan een andere (samengestelde) tabel, maar het is een eenvoudig koppelen van gegevens: je kan geen bewerkingen per rij uitvoeren en op basis van die uitkomst een nieuwe tabel samenstellen. Hiervoor kan je bijvoorbeeld CROSS APPLY of OUTER APPLY gebruiken.

JOIN

Eerst de verschillende vormen van JOIN: CROSS JOIN om alle rijen in een tabel aan alle rijen van een andere rij te koppelen. INNER JOIN om alleen de rijen terug te geven die in beide tabellen terug te geven. LEFT (OUTER) JOIN, waarbij de alle rijen uit de eerste tabel worden getoond en alle rijen uit de tweede tabel die gekoppeld kunnen worden aan de eerste. RIGHT (OUTER) JOIN doet het omgekeerde en FULL (OUTER) JOIN geeft alle rijen terug die eventueel aan elkaar gekoppeld kunnen worden (alle rijen uit de eerste tabel, alle rijen uit de tweede tabel en gekoppeld als dat kan).

Deze beschrijvingen zijn nogal summier en vaag, dus hieronder enkele voorbeelden om het wat duidelijker te maken:

CREATE TABLE _Tabel1 (ID int, Naam nvarchar(50));
CREATE TABLE _Tabel2 (ID int, Naam2 nvarchar(50));

INSERT _Tabel1 (ID, Naam) VALUES (1, 'Anakin'), (2, 'Luke');
INSERT _Tabel2 (ID, Naam2) VALUES (1, 'Dagobah'), (3, 'Tatooine');

SELECT * FROM _Tabel1;
SELECT * FROM _Tabel2;

Het resultaat hiervan is:

Een kort voorbeeld per JOIN:

-- CROSS JOIN
SELECT * FROM _Tabel1
CROSS JOIN _Tabel2
;

-- INNER JOIN
SELECT * FROM _Tabel1
INNER JOIN _Tabel2 ON _Tabel2.ID = _Tabel1.ID

-- LEFT (OUTER) JOIN
SELECT * FROM _Tabel1 T1
LEFT JOIN _Tabel2 ON _Tabel2.ID = T1.ID

-- RIGHT (OUTER) JOIN
SELECT * FROM _Tabel1 AS T1
RIGHT JOIN _Tabel2 AS T2 ON T2.ID = T1.ID

-- FULL (OUTER) JOIN
SELECT * FROM _Tabel1 AS T1
FULL JOIN _Tabel2 AS T2 ON T2.ID = T1.ID

Het resultaat hiervan is:

CROSS en OUTER APPLY

Met CROSS APPLY pas je een bewerking toe op elke rij van een tabel en krijg je als resultaat een nieuwe tabel terug, of zoals Microsoft het stelt: “The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.”

Je kan APPLY toepassen op een tabel of verwijzen naar een functie.

CROSS APPLY op basis van een tabel:

SELECT _Tabel1.*, X.* FROM _Tabel1
CROSS APPLY(
        SELECT 1 + _Tabel1.ID as [NewID]
       UNION ALL
       SELECT 2 + _Tabel1.ID as [NewID]
) X

Resultaat:

CROSS APPLY met een functie:

CREATE OR ALTER FUNCTION dbo._HaalDataUitAndereTabel(@ID int)
RETURNS @NieuweTabel TABLE(
       Name2 nvarchar(50)
) AS
BEGIN

       INSERT INTO @NieuweTabel
       SELECT Naam2 FROM _Tabel2
             WHERE ID = @ID OR ID = 3

       RETURN;
END;

GO

SELECT _Tabel1.*, X.* FROM _Tabel1
CROSS APPLY dbo._HaalDataUitAndereTabel(_Tabel1.ID) X

Resultaat:

Met CROSS APPLY zorg je ervoor dat de rijen in de eerste tabel alleen worden teruggegeven als er minstens één resultaat out de APPLY-operator komt. Als je alle rijen uit de eerste tabel wilt tonen, ongeacht of APPLY iets teruggeeft, zoals een LEFT JOIN, dan gebruik je een OUTER APPLY:

CREATE OR ALTER FUNCTION dbo._HaalDataUitAndereTabel(@ID int)
RETURNS @NieuweTabel TABLE(
       Name2 nvarchar(50)
) AS

BEGIN
       INSERT INTO @NieuweTabel
       SELECT Naam2 FROM _Tabel2
             WHERE ID = @ID OR ID = 2

       RETURN;
END;

GO

SELECT _Tabel1.*, X.* FROM _Tabel1
OUTER APPLY dbo._HaalDataUitAndereTabel(_Tabel1.ID) X

Resultaat:

CROSS en OUTER APPLY bieden dus meer mogelijkheden ten opzichte van JOINS als het gaat om bewerkingen en het opgeven van parameters. Je zou eenvoudig bijvoorbeeld een TOP kunnen plaatsen in een APPLY om een beperkt aantal rijen (zoals bestelregels van bestellingen) terug te geven. Een ander voordeel is dat je functies kunt aanroepen in plaats van tables.