MS Sql 70-461: Chapter 4

Les 1

Cross Joins

Een cross join envoudigste join en heeft als uitkomst een cartesisch product van de twee tabellen. Dit betekent dat elke rij van de ene tabel wordt gecombineerd met alle rijen van de andere tabel.
SELECT x.name AS foo, y.n AS bar
FROM T1 AS x
CROSS JOIN T2 AS y

Een oudere schrijfwijze voor een CROSS JOIN isdoor de tabelnamen te scheiden met een comma: SELECT username FROM T1, T2.

Inner Joins

Met een INNER JOIN kunnen rijen gematched worden tussen twee tabellen op basis van een predicate, wat vaak een primary key(PK) in de ene tabel is en een foreign key (FK) in de andere.

Een equjoin is een join waarbij een predicate wordt gebruikt met een equality operator, zoals T1.kolom = T2.kolom. De inner join geeft enkel de rijen terug waarvoor de predicate true oplevert.

SELECT y.name, p.product

FROM T1 as y

INNER JOIN T2 products as p ON y.productId = p.Id

WHERE y.country = 'nl'

Als een PK wordt aangemaakt, dan wordt er door Sql Server ook een unique index aangemaakt. Als er een FK wordt aangemaakt, dan maakt Sql Server geen extra indexes aan.

Het maakt voor Sql Server niet uit waar de predicate in een inner join zich bevindt: in de ON of in de WHERE, beide geven dezelfde uitkomst. De volgende query geeft hetzelfde resultaat als de volgende query:

SELECT y.name, p.product

FROM T1 as y

INNER JOIN T2 products as p ON y.productId = p.Id AND y.country = 'nl'

Omdat de inner join de meest gebruikte join is, kan het woordje INNER worden weggelaten.

Outer Join

Met een outer join worden altijd alle rijen teruggegeven, of zij nu wel of geen match hebben met de gejoinde tabel. met LEFT OUTER JOIN (of korter LEFT JOIN) worden altijd alle rijen teruggegeven van de linker tabel, of deze nu wel of geen match hebben met de rechter tabel maakt niet uit. De rijen die geen match hebben, krijgen NULL in de kolommen.

SELECT name

FROM employees AS e

LEFT JOIN employees as m ON e.managerId = m.Id

Bij een outer join kennen de ON en de WHERE verschillende rollen. De WHERE dient als filter, terwijl de ON als match predicate dient. Dit betekent dat een ON geen rijen negeert, maar enkel bepaalt welke rijen een match zijn. De WHERE daarentegen negeert wel rijen als deze niet voldoen aan de predicate.

De RIGHT OUTER JOIN (of korter RIGHT JOIN) is hetzelfde als de LEFT JOIN, maar dan voor de rechter tabel.

Een FULL OUTER JOIN is een LEFT en RIGHT join in één:

Multi-Join queries

Een join vind eens per keer plaats tussen twee tabellen. Een multi-join evalueert queries van links naar rechts, Dus het resultaat van de ene join is de input van de volgende join. Let wel: stel dat een LEFT JOIN gevolgd wordt door een INNER JOIN, dan kan het zijn dat de INNERT JOIN geen matches kan maken met het resultaat van de LEFT JOIN. Dit heeft als gevolg dat de rijen met NULL niet worden geretourneerd. Dit kan worden opgelost met een subquery:

SELECT...

FROM...

LEFT JOIN (T1 as y JOIN T2 as z ON y.id = z.foreignkey)

ON ...

WHERE...

Les 2

Subqueries

Subqueries kunnen zelfstandig zijn, maar ook een referentie hebben naar de buitenste query. Het resultaat van een subquery kan een enkele waarde zijn, een aantal waarden of een tabelwaarde.

Een self-contained query is een query die zelfstandig kan worden uitgevoerd. Dit houdt in dat er geen relatie is met andere (buitenste) queries.

Een correlated query is een query die een referentie heeft naar een kolom van de buitenste query. De rij van de buitenste query wordt de correlation genoemd.

De EXISTS predicate accepteert een subquery als input en retourneert true als de subquery minimaal één record bevat, anders false.

Omdat EXSITS true of false teruggeeft wordt de subquery door Sql Server genegeerd en dus vindt er geen optimization plaats.

Table Expressions

Table Expressions zijn queries met een naam.Je schrijft een inner query die een resultaat oplevert en die geef je een naam en daar query je op verder vanaf een outer query. T-Sql kent vier vormen van table expressies:

- Derived tables

- Common Table Expressions (CTE)

- Views

- Inline table valued functions

De eerste twee worden in een statement gebruikt en de laatste twee zijn objecten in de database die hergebruikt kunnen worden.

Derived tables

Een derived table word gespecificeerd in de FROM clause:

SELECT res FROM (SELECT x AS res FROM bar WHERE id = 1) AS d

Een derived table is handig als je bijv wilt filteren als ROW_NUMBER wordt toegepast. Op de alias van ROW_NUMBER kan geen filter worden toegepast. Om toch te kunnen filteren, kan er een derived table van gemaakt worden.

Een derived table kent interne en externe aliases. In bovenstaand voorbeeld wordt gebruik gemaakt van een interne alias (res). Externe aliases worden opgegeven na de alias van de derived table (d):

SELECT foo FROM (SELECT x AS res FROM bar WHERE id = 1) AS d(foo)

De externe aliases moeten in dezelfde volgorde opgegeven worden als de volgorde van de SELECT van de derived table.

CTEs

Het concept van een CTE is hetzelfde als de derived table in die zin dat het een named table expression is en enkel zichtbaar is voor het statement dat hem gemaakt heeft. Ook kent de CTE dezelfde drie delen:

- de inner query

- de naam

- een outer query

Het verschil bij een CTE is dat de volgorde anders is dan bij een derived table: eerst geef je de CTE een naam, dan wordt de inner query gespecificeerd en daarna wordt de outer query gespecificeerd:

WITH

(

)

De voorgaande derived table query kan herschreven worden als een CTE:

WITH d

(

SELECT x AS res FROM bar WHERE id = 1

)

SELECT res FROM d

CTEs kennen ook een recursieve vorm. De body van de CTE kent dan twee queries. Deze queries worden vaak gescheiden middels de UNION ALL operator. Een van de queries is een anchor member. Deze query wordt één keer aangeroepen en geeft een geldige set terug. De andere query(/queries) is de recursive member. Deze query wordt herhaaldelijk aangeroepen totdat het een lege set teruggeeft. In elke iteratie bevat de naam van de CTE het resultaat van de vorige iteratie. De naam van de CTE in de outer query bevat het resultaat van alle iteraties.

WITH cte

(

SELECT empId, empName FROM employees

UNION ALL

SELECT empId, enpName

FROM cte as e

JOIN employees as m ON e.managerId = m.empId

)

SELECT empId, empName FROM cte

Deze CTE geeft alle employees terug met daarbij de naam van hun manager.

Views en Inline Table-Valued functies

Views en inline table-valued functies zijn database objecten en kunnen daarom herbruikt worden en er kunnen permissies op gezet worden. Het belangrijkste verschil is dat een view geen parameters kan accepteren en een functie wel.

Bij een View wordt niet het resultaat van de query opgesagen, maar de definitie van de view.

Apply

Apply lijkt op een join. Het verschil is dat met APPLY een correlation kan worden gelegd met de linker tabel. De inner query van de rechtertabel kan eenr referentie hebben naar de linker tabel. De rechter tabel is bij APPLY een table expression.

APPLY kent twee vormen: CROSS en OUTER. Deze vormen lijken sterk op respectievelijk een INNER JOIN en een LEFT JOIN. Als in een inner query NULL terugkomt bij een CROSS APPLY dan wordt deze rij niet meegenomen (net als bij een INNER JOIN). Een OUTER JOIN neemt deze rijen wel mee (net als bij een LEFT JOIN).

SELECT S.supplierid, S.companyname AS supplier, A.*

FROM Production.Suppliers AS S

CROSS APPLY (SELECT productid, productname, unitprice

FROM Production.Products AS P

WHERE P.supplierid = S.supplierid

ORDER BY unitprice, productid

OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS A

WHERE S.country = N'Japan';

Met een APPLY kan een voorwaarde van de outer query gelegd worden tegen elke rij van het resultaat van de inner query.

Les 3

T-Sql kent drie set operators:

- UNION

- INTERSECT

- EXCEPT

De volgende regels gelden voor het toepassen van set operators:

- Het aantal, de volgorde en het type van de kolommen van elke query moet gelijk zijn

- Set operatord beschouwen twee NULLs als gelijkwaardig

- Set operators zijn geen cursor operators dus de queries kunnen geen ORDERBY gebruiken

- De namen van de kolommen worden bepaald door de eerste query.

Set operators kennen een volgorde van uitvoeren. Als eerste wordt altijd INTERSECT uitgevoerd en UNION en EXCEPT worden van links naar rechts uitgevoerd.

Union en Union All

UNION voegt een of meerdere resultaatsets samen en past daarop een DISTINCT toe wat er voor zorgt dat er geen dubbele rijen in de totaalset zitten. UNION ALL past geen DISTINCT toe, dus kan de resultaat set dubbele rijen bevatten.

Intersect

INTERSECT geeft enkel de overeenkomende rijen terug en voert daarbij een DISTINCT uit. Met andere woorden als een rij een of meerdere keren voorkomt in allebei de sets, dan wordt deze rij geretourneerd. NULLs worden als gelijkwaardig beschouwd. Dus als beide sets NULL teruggeven, dan zal deze rij worden meegenomen door INTERSECT.

Except

EXCEPT geeft enkel rijen terug die wel in de ene set voorkomen, maar niet in de andere.

Zoeken in deze blog

Kennisdump