Set-operatoren in SQL: een allesomvattende gids

Wat zijn set-bewerkingen in SQL?

Set-bewerkingen in SQL zijn technieken om de resultaten van twee of meer SELECT-statements te combineren of te vergelijken. Ze werken als wiskundige verzamelbewerkingen, waarmee we de unie, doorsnede of het verschil kunnen vinden tussen de rijen die onze queries teruggeven. Dit maakt ze onmisbaar bij het analyseren van data uit meerdere bronnen of perspectieven.

Hier is een snel overzicht van de kernset-bewerkingen:

• UNION: Voegt alle unieke rijen uit twee of meer SELECT-statements samen en verwijdert duplicaten.

• UNION ALL: Voegt alle rijen uit twee of meer SELECT-statements samen en behoudt duplicaten.

• INTERSECT: Geeft alleen de rijen terug die in beide SELECT-statements voorkomen.

• EXCEPT: Geeft rijen terug uit het eerste SELECT-statement die niet in het tweede voorkomen.

Vergelijking met relationele algebra-bewerkingen

Relationele algebra is een theoretisch raamwerk dat de basis vormt voor het begrijpen van databasequeries. Het biedt abstracte bewerkingen zoals projectie, selectie en join, geworteld in wiskundige principes en onafhankelijk van specifieke databasesystemen. Zie het als de logica "achter de schermen" die onze database-interacties aandrijft.

Set-operatoren in SQL bieden een praktische implementatie van deze concepten binnen een database-omgeving. Ze stellen ons in staat om verzamelbewerkingen zoals unie, doorsnede en verschil rechtstreeks uit te voeren op de resultaatsets van SQL-queries.

Hoewel relationele algebra een formele basis biedt voor databasebewerkingen, leveren set-operatoren in SQL een gestandaardiseerde, gebruiksvriendelijke interface voor datamanipulatie.

Het begrijpen van de relatie tussen set-operatoren en relationele algebra-bewerkingen geeft inzicht in de theoretische fundamenten van SQL. Die link helpt je queries te schrijven met een duidelijker mentaal model van wat er onder de motorkap gebeurt.

Typen set-operatoren in SQL

Er zijn drie primaire set-operatoren in SQL:

• UNION

• INTERSECT

• EXCEPT (of MINUS in sommige dialecten)

Deze operatoren komen wiskundig overeen met de concepten unie, doorsnede en verzamelverschil.

Regels voor het gebruik van set-operatoren

Voordat je queries met set-operatoren combineert, moeten vier voorwaarden zijn vervuld. Als je er een mist, zal je query een fout geven.

• Hetzelfde aantal kolommen: Elke SELECT moet hetzelfde aantal kolommen teruggeven. Als de ene query er drie teruggeeft en de andere twee, gooit de database een fout.

• Compatibele datatypen: Overeenkomende kolommen moeten overeenkomende typen hebben. Je kunt geen VARCHAR-naamkolom combineren met een INTEGER-ID op dezelfde positie.

• Dezelfde kolomvolgorde: SQL koppelt kolommen op positie, niet op naam. De eerste kolom van query één koppelt aan de eerste kolom van query twee, ongeacht hoe die kolommen heten.

• ORDER BY alleen aan het einde: ORDER BY mag maar één keer voorkomen, helemaal aan het einde van de gecombineerde query. Je kunt het niet gebruiken binnen afzonderlijke SELECT-statements.

Nog een detail: kolomnamen in de output komen uit het eerste SELECT-statement. Aliassen in latere queries worden genegeerd, dus voeg aliassen alleen toe aan de eerste query als je aangepaste outputnamen nodig hebt.

De UNION-operator gebruiken in SQL

De UNION-operator combineert de resultaten van twee of meer SELECT-queries in één resultaatset en verwijdert standaard dubbele rijen.

Stel bijvoorbeeld dat we twee tabellen hebben, employees en contractors, elk met vergelijkbare kolommen zoals contractors, department en salary. Voor leermomenten bekijken we deze twee voorbeeldtabellen:

employees:

contractors:

We kunnen de resultaten uit beide tabellen combineren met het volgende commando:

-- Using UNION to combine all employees and contractors
SELECT name, department, salary FROM employees
UNION
SELECT name, department, salary FROM contractors;

Deze query selecteert de kolommen name, department en salary uit zowel de tabellen employees als contractors en voegt ze samen tot één resultaatset. De UNION-operator verwijdert automatisch dubbele rijen uit de uiteindelijke resultaatset.

Merk op dat Carol, die in beide tabellen voorkomt, maar één keer in het resultaat staat. Als we beide vermeldingen van Carol willen behouden (met haar verschillende salarissen), gebruiken we UNION ALL.

UNION vs UNION ALL

De UNION-operator verwijdert geen NULL-waarden. Als een kolom NULL-waarden bevat in de ene resultaatset en niet-NULL-waarden in de corresponderende kolom van een andere resultaatset, blijven de NULL-waarden behouden in de uiteindelijke resultaatset die door de UNION-operator wordt geproduceerd.

Als we NULL-waarden in de resultaatset willen opnemen en willen voorkomen dat ze door de UNION-operator worden verwijderd, kunnen we in plaats daarvan de UNION ALL-operator gebruiken. Deze operator combineert de resultaten van meerdere SELECT-queries, inclusief alle rijen uit elke resultaatset, ongeacht of het duplicaten zijn of NULL-waarden bevatten.

De INTERSECT-operator gebruiken in SQL

De INTERSECT-operator geeft alleen de rijen terug die in beide resultaatsets voorkomen. Denk eraan als het vinden van de mensen die tot beide groepen behoren.

Laten we INTERSECT gebruiken om onze tabellen hierboven te bevragen. Voor het voorbeeld bevragen we alleen de kolommen name en department:

-- Using INTERSECT to find common employees
SELECT name, department FROM employees
INTERSECT
SELECT name, department FROM contractors;

Deze query selecteert de kolommen name en department uit zowel de employees- als contractors-tabellen en geeft alleen de rijen terug die in beide tabellen bestaan, gebaseerd op alle geselecteerde kolommen.

De INTERSECT-operator behandelt NULL-waarden volgens standaard vergelijkingsregels en beschouwt NULL-waarden gelijk bij het vergelijken van corresponderende kolommen. Hij resulteert ook in een lege set bij het werken met lege resultaatsets.

Met andere woorden: als een NULL-waarde aanwezig is in de ene resultaatset en de corresponderende kolom in de andere resultaatset een niet-NULL-waarde bevat, worden de rijen niet als gelijk beschouwd – ze worden niet opgenomen in het doorsnederesultaat.

Bovendien, als een van de resultaatsets die aan de INTERSECT-operator worden gegeven leeg is, is het totale resultaat ook leeg. Er zijn geen gemeenschappelijke rijen tussen een lege set en iets anders.

De EXCEPT-operator gebruiken in SQL

De EXCEPT-operator geeft rijen terug uit de eerste resultaatset die niet in de tweede voorkomen.

In Oracle Database wordt EXCEPT geschreven als MINUS; alle andere grote dialecten (PostgreSQL, SQL Server, MySQL 8.0.31+, SQLite) gebruiken EXCEPT.

Stel bijvoorbeeld dat we de volgende query uitvoeren:

-- Using EXCEPT to find employees who are not contractors
SELECT name, department, salary FROM employees
EXCEPT
SELECT name, department, salary FROM contractors;

De kolommen name, department en salary worden geselecteerd uit de tabel employees en geven alleen de rijen terug die niet bestaan in de tabel contractors.

De EXCEPT-operator volgt ook standaard vergelijkingsregels voor het omgaan met NULL-waarden. Het gedrag bij lege resultaatsets levert een lege set op als de eerste resultaatset leeg is, of omvat alle rijen uit de eerste resultaatset als de tweede resultaatset leeg is.

SQL-set-operatoren: prestaties en optimalisatie

De impact van set-operatoren op de queryprestaties in SQL kan variëren afhankelijk van factoren zoals de grootte van de datasets, de complexiteit van de queries en het gebruikte databasebeheersysteem (DBMS).

Laten we de belangrijkste factoren en strategieën voor optimalisatie opsplitsen.

Datavolume en querycomplexiteit

Bij het werken met grote hoeveelheden data kunnen set-operatoren de queryprestaties aanzienlijk beïnvloeden, omdat de grootte van de resultaatsets die moeten worden gecombineerd, doorsneden of vergeleken, de verwerkingstijd vergroot die nodig is om die bewerking uit te voeren.

Complexe queries met meerdere subqueries, joins of set-operatoren kunnen extra verwerkingslast veroorzaken en de queryprestaties beïnvloeden. Gekoppelde bewerkingen of geneste set-bewerkingen kunnen de prestatiegevolgen verder verergeren.

Indexering en optimalisatietechnieken

Een juiste indexering van de kolommen die betrokken zijn bij set-bewerkingen kan de queryprestaties aanzienlijk verbeteren. Indexen helpen de database-engine snel de relevante rijen te vinden en op te halen, waardoor volledige tabelscans worden verminderd en de uitvoeringstijd van queries verbetert.

Om de prestaties van queries met set-operatoren te verbeteren, kunnen databasebeheerders en ontwikkelaars technieken toepassen zoals het herschrijven van queries, analyse van uitvoeringsplannen en schema-optimalisatie. Technieken zoals querycaching en materialized views kunnen ook worden gebruikt om de resultaten van complexe queries vooraf te berekenen en op te slaan, waardoor de rekenlast van set-bewerkingen wordt verminderd.

Database-engine en hardwarebronnen

De prestaties van set-bewerkingen kunnen variëren afhankelijk van de onderliggende database-engine en diens optimalisatiemogelijkheden. Verschillende DBMS'en kunnen verschillende optimalisatiestrategieën en algoritmen gebruiken voor het verwerken van set-bewerkingen, wat leidt tot prestatieverschillen.

De beschikbaarheid van hardwarebronnen zoals CPU, geheugen en schijf-I/O beïnvloedt ook de prestaties van queries met set-operatoren. Voldoende hardwarebronnen kunnen prestatieknelpunten helpen verminderen en zorgen voor efficiënte query-uitvoering.

SQL-set-operatoren in de praktijk

Set-operatoren zijn niet alleen theoretische tools; ze hebben toepassingen in de echte wereld die bedrijfsbeslissingen aanzienlijk kunnen beïnvloeden. Laten we een vereenvoudigd voorbeeld doorlopen van hoe een bedrijf set-operatoren kan gebruiken om zijn klantenbestand te segmenteren voor gerichte marketingcampagnes.

De situatie

Stel je een bedrijf voor dat zowel online als in fysieke winkels verkoopt. Ze hebben twee afzonderlijke datasets:

1. Online aankopen: Klant-ID, aankoopgeschiedenis, demografie en locatie voor online shoppers.
2. Transacties in de winkel: Vergelijkbare informatie voor klanten die fysiek hebben gewinkeld.

Set-operatoren gebruiken

Om een compleet beeld van alle klanten te krijgen, zou het bedrijf eerst UNION gebruiken om beide datasets te combineren in één tabel, waarbij duplicaten worden verwijderd. Dit geeft ze een uniform beeld van hun volledige klantenbestand.

Vervolgens kunnen ze INTERSECT gebruiken om klanten te identificeren die zowel online als in de winkel hebben gewinkeld. Dit segment is bijzonder waardevol omdat deze klanten sterk betrokken zijn bij het merk over meerdere kanalen.

Om kansen voor cross-channel promotie te vinden, kan het bedrijf EXCEPT gebruiken. Bijvoorbeeld, SELECT * FROM online_purchases EXCEPT SELECT * FROM in_store_transactions zou klanten vinden die alleen online hebben gewinkeld en niet in winkels. Het bedrijf kan deze klanten vervolgens targeten met promoties die hen aanmoedigen een fysieke locatie te bezoeken.

Verder dan segmentatie

Met deze segmenten geïdentificeerd kan het bedrijf nog verder gaan en ze verfijnen op basis van aanvullende factoren zoals demografie of aankoopgeschiedenis. Dit gedetailleerde begrip van hun klanten stelt hen in staat om marketingcampagnes gerichter af te stemmen.

Set-operatoren vs. JOINs

Zowel set-operatoren als SQL JOINs combineren data uit meerdere queries, maar ze werken anders. De kernvraag is of je rijen of kolommen wilt combineren.

Beperkingen en aandachtspunten van set-operatoren

Bij het gebruik van set-operatoren in SQL is het belangrijk om verschillende beperkingen en factoren in overweging te nemen die de queryprestaties, de nauwkeurigheid van resultaten en de algehele bruikbaarheid kunnen beïnvloeden.

Datatype-compatibiliteit en NULL-waarden

Overeenkomende kolommen in de resultaatsets moeten compatibele datatypen hebben. Controleer of corresponderende kolommen compatibele datatypen delen voordat je queries combineert. Typemismatches veroorzaken fouten die je makkelijk over het hoofd ziet.

Set-operatoren kunnen NULL-waarden verschillend behandelen, afhankelijk van het DBMS en de specifieke operator. Om fouten te voorkomen, moeten ontwikkelaars begrijpen hoe NULL-waarden worden behandeld.

Prestatie-impact en dubbele rijen

Set-bewerkingen kunnen de queryprestaties aanzienlijk beïnvloeden, vooral bij grote of complexe datasets. Factoren zoals indexering, query-optimalisatie en hardwarebronnen kunnen de prestaties beïnvloeden. Optimalisatietechnieken en performance-tuning zijn essentieel om prestatieknelpunten te beperken.

Standaard verwijderen set-operatoren dubbele rijen uit de resultaatset. In sommige gevallen kan het echter nodig zijn om dubbele rijen te behouden. Het is belangrijk om het gedrag van set-operatoren met betrekking tot duplicaten te begrijpen en waar nodig passende technieken te gebruiken om duplicaten te verwerken.

Sortering van resultaten en geheugenbeperkingen

Set-operatoren garanderen niet de volgorde van de resultaten in de uiteindelijke output. Om het gecombineerde resultaat te sorteren, voeg je helemaal aan het einde van de volledige query, na het laatste SELECT-statement, een ORDER BY-clausule toe.

Set-bewerkingen kunnen veel geheugen en resources verbruiken, vooral bij grote datasets. Geheugenbeperkingen en resourcelimieten moeten worden meegewogen om prestatieverslechtering of systeeminstabiliteit te voorkomen.

Complexiteit, onderhoudbaarheid en DBMS-overstijgende compatibiliteit

Complexe queries met meerdere set-operatoren, subqueries en joins kunnen moeilijk te begrijpen, te onderhouden en te debuggen zijn. Om de leesbaarheid en onderhoudbaarheid te verbeteren, moeten queries beknopt, goed gedocumenteerd en modulair zijn.

Set-operatoren kunnen variaties hebben in syntaxis en gedrag tussen verschillende databasebeheersystemen (DBMS'en). Deze verschillen kennen is cruciaal bij het schrijven van SQL-queries voor cross-platform compatibiliteit.

Tot slot

Set-operatoren lossen een specifiek probleem op: resultaatsets combineren of vergelijken zonder vooraf te weten welke rijen overlappen.

Ze omvatten UNION, INTERSECT en EXCEPT: de drie operatoren voor respectievelijk combineren, vergelijken en aftrekken van resultaatsets.

Wil je meer leren, bekijk dan deze cursus over Data joinen in SQL.