Wat is SQL? De lingua franca van gegevensanalyse

Heden ten dage is Structured Query Language het standaard middel voor het manipuleren en bevragen van gegevens in relationele databases, zij het met propriëtaire uitbreidingen onder de producten. Het gemak en de alomtegenwoordigheid van SQL hebben er zelfs toe geleid dat de makers van veel “NoSQL” of niet-relationele data opslagsystemen, zoals Hadoop, subsets van SQL hebben overgenomen of hun eigen SQL-achtige query talen hebben ontwikkeld.

Maar SQL was niet altijd de “universele” taal voor relationele databases. Vanaf het begin (rond 1980), had SQL een aantal tegenslagen. Veel onderzoekers en ontwikkelaars in die tijd, waaronder ikzelf, dachten dat de overhead van SQL zou verhinderen dat het ooit praktisch zou zijn in een produktiedatabase.

Het is duidelijk dat we het mis hadden. Maar velen geloven nog steeds dat, voor al het gemak en de toegankelijkheid van SQL, de prijs in runtime performance vaak te hoog is.

SQL geschiedenis

Voordat er SQL was, hadden databases strakke, navigeerbare programmeer interfaces, en werden ze meestal ontworpen rond een netwerk schema genaamd het CODASYL data model. CODASYL (Committee on Data Systems Languages) was een consortium dat verantwoordelijk was voor de COBOL programmeertaal (beginnend in 1959) en database taaluitbreidingen (beginnend 10 jaar later).

Wanneer je tegen een CODASYL database programmeerde, navigeerde je naar records via sets, die één-op-veel relaties uitdrukken. Oudere hiërarchische databases staan alleen toe dat een record tot één set behoort. Netwerkdatabases staan toe dat een record tot meerdere sets behoort.

Stel dat je een lijst wilt maken van de studenten die staan ingeschreven voor CS 101. Eerst zou u "CS 101" in de Courses set op naam vinden, die als eigenaar of ouder van de Enrollees set instellen, zoek het eerste lid (ffm) van de Enrollees set, dat is een Student record, en maak er een lijst van. Dan zou je in een lus gaan: Zoek het volgende lid (fnm) en maak er een lijst van. Als fnm mislukt, verlaat je de lus.

Dat lijkt misschien een hoop werk voor de database-programmeur, maar het was zeer efficiënt in uitvoeringstijd. Deskundigen als Michael Stonebraker van de University of California in Berkeley en Ingres wezen erop dat het uitvoeren van zo’n query in een CODASYL database zoals IDMS ongeveer de helft van de CPU-tijd en minder dan de helft van het geheugen kostte als dezelfde query in een relationele database met SQL.

Ter vergelijking, de equivalente SQL-query om alle studenten in CS 101 terug te vinden zou zoiets zijn als

SELECT student.name FROM courses, enrollees, students WHERE course.name ="CS 101"

Die syntax impliceert een relationele inner join (eigenlijk twee van hen), zoals ik hieronder zal uitleggen, en laat een aantal belangrijke details weg, zoals de velden die voor de joins worden gebruikt.

Relationele databases en SQL

Waarom zou je een factor twee verbetering in uitvoeringssnelheid en geheugengebruik opgeven? Er waren twee grote redenen: ontwikkelgemak en portabiliteit. Ik dacht niet dat een van beide er veel toe deed in 1980 in vergelijking met prestaties en geheugenvereisten, maar naarmate computerhardware verbeterde en goedkoper werd, gaven mensen niet meer om uitvoeringssnelheid en geheugen en maakten ze zich meer zorgen over de kosten van ontwikkeling.

Met andere woorden, de Wet van Moore doodde CODASYL databases ten gunste van relationele databases. De verbetering in ontwikkelingstijd was aanzienlijk, maar de overdraagbaarheid van SQL bleek een utopie.

Waar komen het relationele model en SQL vandaan? E.F. “Ted” Codd was een computerwetenschapper bij het IBM San Jose Research Laboratory die in de jaren zestig de theorie van het relationele model uitwerkte en deze in 1970 publiceerde. IBM was traag met het implementeren van een relationele database in een poging om de inkomsten van zijn CODASYL database IMS/DB te beschermen. Toen IBM eindelijk begon aan zijn System R project, was het ontwikkelingsteam (Don Chamberlin en Ray Boyce) niet onder Codd, en zij negeerden Codd’s Alpha relationele taal paper uit 1971 om hun eigen taal te ontwerpen, SEQUEL (Structured English Query Language). In 1979, nog voor IBM zijn product had uitgebracht, nam Larry Ellison de taal op in zijn Oracle database (gebruikmakend van IBM’s SEQUEL publicaties van voor de lancering als zijn spec). SEQUEL werd al snel SQL om een internationale schending van het handelsmerk te voorkomen.

De “toms-kloppen voor SQL” (zoals Michael Stonebraker het uitdrukte) kwamen niet alleen van Oracle en IBM, maar ook van klanten. Het was niet gemakkelijk om CODASYL databaseontwerpers en programmeurs in te huren of op te leiden, dus SEQUEL (en SQL) zagen er veel aantrekkelijker uit. SQL was zo aantrekkelijk in de latere jaren ’80 dat veel database leveranciers een SQL query processor bovenop hun CODASYL databases nietten, tot groot ongenoegen van Codd, die vond dat relationele databases vanaf nul ontworpen moesten worden om relationeel te zijn.

Een pure relationele database, zoals ontworpen door Codd, is gebouwd op tuples gegroepeerd in relaties, consistent met eerste-orde predicatenlogica. Relationele databases in de echte wereld hebben tabellen met velden, constraints en triggers, en tabellen zijn met elkaar verbonden via foreign keys. SQL wordt gebruikt om aan te geven welke gegevens moeten worden geretourneerd, en een SQL-queryprocessor en query-optimalisator zetten de SQL-declaratie om in een query-plan dat door de database-engine wordt uitgevoerd.

SQL omvat een subtaal voor het definiëren van schema’s, de datadefinitie-taal (DDL), samen met een subtaal voor het wijzigen van gegevens, de datamanipulatie-taal (DML). Beide hebben hun oorsprong in vroege CODASYL-specificaties. De derde subtaal in SQL verklaart queries, door middel van het SELECT statement en relationele joins.

SQL SELECT statement

Het SELECT statement vertelt de query optimizer welke gegevens moeten worden teruggegeven, in welke tabellen moet worden gezocht, welke relaties moeten worden gevolgd, en welke volgorde moet worden opgelegd aan de geretourneerde gegevens. De query optimizer moet zelf uitzoeken welke indexen hij moet gebruiken om brute force table scans te voorkomen en een goede query performance te bereiken, tenzij de specifieke database index hints ondersteunt.

Een deel van de kunst van relationele database ontwerp hangt af van het oordeelkundige gebruik van indexen. Als je een index weglaat voor een veel voorkomende query, kan de hele database trager worden bij zware leesbelasting. Als je te veel indexen hebt, kan de hele database trager worden bij zware schrijf- en update-opdrachten.

Een andere belangrijke kunst is het kiezen van een goede, unieke primaire sleutel voor elke tabel. Je moet niet alleen rekening houden met de impact van de primaire sleutel op gewone queries, maar ook met hoe hij zal spelen in joins als hij als een foreign key in een andere tabel verschijnt, en hoe hij de locality of reference van de gegevens zal beïnvloeden.

In het geavanceerde geval van databasetabellen die worden opgesplitst in verschillende volumes, afhankelijk van de waarde van de primaire sleutel, horizontale sharding genoemd, moet je ook rekening houden met hoe de primaire sleutel van invloed zal zijn op de sharding. Hint: Je wilt de tabel gelijkmatig over volumes verdelen, wat suggereert dat je geen datumstempels of opeenvolgende gehele getallen als primaire sleutels wilt gebruiken.

Discussies over het SELECT statement beginnen misschien eenvoudig, maar kunnen al snel verwarrend worden. Overweeg:

SELECT * FROM Customers;

Eenvoudig, toch? Het vraagt om alle velden en alle rijen van de Customers tabel. Stel echter dat de Customers tabel honderd miljoen rijen en honderd velden heeft, en een van de velden is een groot tekstveld voor commentaar. Hoe lang duurt het om al die gegevens over een netwerkverbinding van 10 megabit per seconde op te halen, als elke rij gemiddeld 1 kilobyte aan gegevens bevat?

Misschien moet je de hoeveelheid gegevens die je over de kabel verstuurt, beperken. Overweeg:

SELECT TOP 100 companyName, lastSaleDate, lastSaleAmount, totalSalesAmount FROM Customers
WHERE state ="Ohio" AND city ="Cleveland"
ORDER BY lastSaleDate DESCENDING;

Nu ga je een stuk minder gegevens ophalen. Je hebt de database gevraagd om je slechts vier velden te geven, om alleen de bedrijven in Cleveland in aanmerking te nemen, en om je alleen de 100 bedrijven met de meest recente verkopen te geven. Om dat zo efficiënt mogelijk te doen op de database server, echter, heeft de Customers tabel een index nodig op state+city voor de WHERE clausule en een index op lastSaleDate voor de ORDER BY en TOP 100 clausules.

Tussen haakjes, TOP 100 is geldig voor SQL Server en SQL Azure, maar niet voor MySQL of Oracle. In MySQL gebruikt u LIMIT 100 na de WHERE-clausule. In Oracle zou u een bound op ROWNUM gebruiken als onderdeel van de WHERE clausule, d.w.z. WHERE... AND ROWNUM <=100. Helaas gaan de ANSI/ISO SQL standaarden (en daar zijn er negen van, van 1986 tot 2016) maar tot zover, waarna elke database zijn eigen clausules en mogelijkheden introduceert.

SQL joins

Tot nu toe heb ik de SELECT syntax voor enkele tabellen beschreven. Voordat ik JOIN clausules kan uitleggen, moet je foreign keys en relaties tussen tabellen begrijpen. Ik zal dit uitleggen aan de hand van voorbeelden in DDL, met gebruik van SQL Server syntax.

De korte versie hiervan is vrij eenvoudig. Elke tabel die je in relaties wilt gebruiken, moet een primary key constraint hebben; dit kan een enkel veld zijn of een combinatie van velden gedefinieerd door een expressie. Bijvoorbeeld:

CREATE TABLE Persons (
PersonID int NOT NULL PRIMARY KEY,
PersonName char(80),
...

Elke tabel die moet relateren aan Persons moet een veld hebben dat overeenkomt met de Persons primaire sleutel, en om de relationele integriteit te bewaren moet dat veld een foreign key constraint hebben. Bijvoorbeeld:

CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
...
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

Er zijn langere versies van beide statements die het CONSTRAINT sleutelwoord gebruiken, waarmee je de constraint een naam kunt geven. Dat is wat de meeste database ontwerptools genereren.

Primary keys zijn altijd geïndexeerd en uniek (de veldwaarden kunnen niet worden gedupliceerd). Andere velden kunnen optioneel worden geïndexeerd. Het is vaak nuttig om indexen te maken voor foreign key velden en voor velden die voorkomen in WHERE en ORDER BY clausules, hoewel niet altijd, vanwege de potentiële overhead van writes en updates.

Hoe zou je een query schrijven die alle orders van John Doe teruggeeft?

SELECT PersonName, OrderID FROM Persons
INNER JOIN Orders ON Persons.PersonID = Orders.PersonID
WHERE PersonName ="John Doe";

In feite zijn er vier soorten JOININNEROUTERLEFT, en RIGHT. De INNER JOIN is de standaard (u kunt het woord INNER weglaten), en het is degene die alleen rijen bevat die overeenkomende waarden in beide tabellen bevatten. Als je een lijst wilt maken van personen die al dan niet een order hebben, gebruik je een LEFT JOIN, bijvoorbeeld:

SELECT PersonName, OrderID FROM Persons
LEFT JOIN Orders ON Persons.PersonID = Orders.PersonID
ORDER BY PersonName;

Als je queries gaat doen die meer dan twee tabellen samenvoegen, die expressies gebruiken, of die datatypes coercen, dan kan de syntax in het begin een beetje lastig worden. Gelukkig zijn er database ontwikkel tools die correcte SQL queries voor je kunnen genereren, vaak door tabellen en velden van het schema diagram naar een query diagram te slepen.

SQL stored procedures

Soms brengt het declaratieve karakter van het SELECT statement je niet waar je naar toe wilt. De meeste databases hebben een faciliteit die stored procedures heet; helaas is dit een gebied waar bijna alle databases gebruik maken van eigen uitbreidingen op de ANSI/ISO SQL standaarden.

In SQL Server was het aanvankelijke dialect voor stored procedures (of stored procs) Transact-SQL, aka T-SQL; in Oracle was het PL-SQL. Beide databases hebben extra talen toegevoegd voor stored procedures, zoals C#, Java, en R. Een eenvoudige T-SQL stored procedure kan slechts een geparametriseerde versie zijn van een SELECT statement. De voordelen zijn gebruiksgemak en efficiëntie. Stored procedures worden geoptimaliseerd wanneer ze worden opgeslagen, niet iedere keer dat ze worden uitgevoerd.

Een meer gecompliceerde T-SQL stored procedure kan gebruik maken van meerdere SQL statements, input en output parameters, lokale variabelen, BEGIN...END blokken, IF...THEN...ELSE condities, cursors (rij-voor-rij verwerking van een set), expressies, tijdelijke tabellen, en een hele reeks andere procedurele syntaxis. Het is duidelijk dat als de opgeslagen procedure taal C#, Java, of R is, je de functies en syntax van die procedurele talen gaat gebruiken. Met andere woorden, ondanks het feit dat de motivatie voor SQL was om gestandaardiseerde declaratieve queries te gebruiken, zie je in de echte wereld veel database-specifieke procedurele server programmering.

Dat brengt ons niet helemaal terug naar de slechte oude tijd van CODASYL database programmering (hoewel cursors in de buurt komen), maar het neemt wel afstand van de ideeën dat SQL statements gestandaardiseerd moeten zijn en dat performance zorgen moeten worden overgelaten aan de database query optimizer. Uiteindelijk is een verdubbeling van de performance vaak te veel om op tafel te laten liggen.

Leer SQL

De onderstaande sites kunnen u helpen SQL te leren, of de eigenaardigheden van verschillende SQL dialecten te ontdekken.

Codecademy. Leer SQL. Gratis en interactief. Pro-upgrade beschikbaar tegen betaling.
Khan Academy. Intro tot SQL: Query’s en het beheren van gegevens. Gratis video tutorial.
SoloLearn. SQL grondbeginselen. MySQL-georiënteerd. Gratis.
SQL Problemen en oplossingen en SQL-oefeningen. Interactief tekstboek en oefeningen. Gratis.
SQLZoo. Een interactieve SQL tutorial ontwikkeld en onderhouden door Edinburgh Napier University. Ondersteunt Microsoft SQL Server, Oracle Database, MySQL, IBM DB2, en PostgreSQL. Gratis.
Tutorialspoint. Leer SQL. Alleen tekst, niet interactief. Gratis.
Udacity. Intro tot Relationele Databases. Gebruikt Python en vereist enige Python-kennis. Gratis.
Udemy. Gratis cursussen zijn onder meer Inleiding tot databases en SQL Querying, MySQL Database voor Beginners, Microsoft SQL voor Beginners, Hands-on SQL voor Beginners (SELECTFROM, en WHERE), en Sachin leert snel (SQL).
Vertabelo Academy. SQL Basics, Operating on Data in SQL, Creating Tables in SQL, en acht andere interactieve SQL cursussen. Sommige cursussen hebben gratis testritten, daarna kan er een vergoeding worden gevraagd. Er zijn zeven extra cursussen voor Microsoft SQL Server. De site heeft ook een grafisch databaseontwerpgereedschap voor PostgreSQL, MySQL, Oracle Database, SQL Server, SQLite en IBM DB2.
W3Schools. SQL-handleiding. Gratis en beschikbaar zonder registratie.

Database documentatie: