Overzichtelijk SQL

Inleiding

Dit artikel is een toevoeging op Overzichtelijk programmeren. Net als overzichtelijke PHP code schrijven, is het duidelijk houden van een SQL-Query een belangrijk iets. Hoe vaak is het niet voorkomen dat je een database foutmelding krijgt, en het probleem een ontbrekende komma of haakje was?

Het maakt voor de verwerkingssnelheid helemaal niet uit of alle SQL op één regel staat of wordt verdeeld over meerdere regels, dus schrijf de query duidelijk op, dat maakt jouw leven een stuk makkelijker en je applicatie wordt er niet trager door.

De basisregels

• Gebruik hoofdletters voor alle SQL keywords, zoals SELECT , DELETE, FROM, WHERE, LIMIT etc.

• Gebruik voor tabel- en kolomnamen altijd dezelfde soort letters, alleen kleine letters of alleen hoofdletters, maar nooit door elkaar.

• Spring in na elk SQL keyword, en terug voor elk nieuw keyword.

• Som lijsten parameters op in aparte regels met de komma aan het begin van de regel in plaats van erachter. De reden is dat je in de praktijk tijdens het debuggen vaak het laatste item van de lijst af wilt halen. Als de komma achter elk item staat dan moet je ook de komma van het vorige item weghalen. Door de komma voor elk item te zetten kun je de hele regel uitcommentarieeren zonder een komma weg te hoeven halen.

Met een komma erachter:

SELECT 
  fiets,
  auto -- ,
-- vliegtuig
FROM
  voertuigen;

met een komma ervoor:

SELECT 
  fiets
  , auto
-- , vliegtuig
FROM
  voertuigen;

Een bijkomend voordeel is dat alle komma's onder elkaar komen te staan waardoor je goed kunt zien of je er nog een mist.

• Subqueries spring je zover mogelijk net zover in als het statement waar ze in vallen:

SELECT
  veld
  , veld2
  , veld3
FROM
  tabel
WHERE
  gebruiker = 'piet'
AND
  geheim IN (
              SELECT
                geheimid
              FROM
                geheimentabel
              WHERE
                magie='groot'
            )

Voorbeeld

Stel je hebt deze query:

SELECT veld, veld2, veld3 FROM tabel WHERE gebruiker='piet'

De correcte opmaak wordt dan:

SELECT
  veld
  , veld2
  , veld3
FROM
  tabel
WHERE
  gebruiker = 'piet'

Het voordeel van deze schrijfwijze wordt bijzonder snel duidelijk als de query wat langer wordt:

SELECT dayofcourse > courseduration, dayofcourse > (courseduration+14) FROM ( SELECT SUM(duration) AS courseduration, courseid FROM m3_psy_timeframes GROUP BY courseid) AS courseinfo INNER JOIN ( SELECT SQL_NO_CACHE users.uid, subscriptions.courseid, (DATEDIFF(NOW() , startdate) - totalpauze) AS dayofcourse ... <knip want dit valt toch buiten beeld>

Dat is niet meer te volgen. Een nette indeling houdt het overzichtelijk:

SELECT
  dayofcourse > courseduration
  dayofcourse > (courseduration+14)
FROM
  (
    SELECT
      SUM(duration) AS courseduration
      , courseid
    FROM
      m3_psy_timeframes 
    GROUP BY
      courseid
  ) AS courseinfo
INNER JOIN
  (
    SELECT SQL_NO_CACHE
      users.uid
      , subscriptions.courseid
      , (DATEDIFF(NOW() , startdate) - totalpauze) AS dayofcourse
    FROM
      m3_users AS users
    INNER JOIN
      m3_psy_course_subscriptions AS subscriptions
    ON 
      subscriptions.userid = users.uid
    INNER JOIN
      (
        SELECT
          uid
          , subscriptions.courseid
          , COALESCE(SUM(DATEDIFF(pauzes.enddate , pauzes.startdate)),0) AS totalpauze
        FROM
          m3_users AS users
        INNER JOIN
          m3_psy_course_subscriptions AS subscriptions
        ON
          subscriptions.userid = users.uid
        LEFT JOIN
          m3_psy_course_pauses AS pauzes
        ON
          pauzes.userid=users.uid
        GROUP BY
          users.uid
          , subscriptions.courseid
      ) AS adata
    ON
      adata.uid=users.uid
    AND
      adata.courseid=subscriptions.courseid
  )AS bdata
ON
  bdata.courseid = courseinfo.courseid

Regelnummers

Zou er een fout in je query staan, dan wordt daarin doorgaans ook een regelnummer gegeven. Dit regelnummer verwijst naar de regel in de query. Als een query maar uit 1 regel bestaat, krijg je dan dus altijd de verwijzing "op regel 1". Spreiden van je query over meerdere regels zal dan het zoeken van de fout versnellen.

Backticks ( ` )

Backticks zijn een MySQL handigheidje om aan te geven dat de tekst tussen de backticks niet mag worden gelezen als een SQL keyword, ookal is het dat wel. Vooral PHPMyAdmin past deze dingen te pas en te onpas toe.

Dit levert allerlei spannende bugs op omdat die backticks gegarandeerd een keer vergeten worden. Stel dat je een tabel hebt met een kolom genaamd delete en die wil je opvragen met een SELECT query, dan doe je:

SELECT 
  `delete`
FROM
  tabelnaam

Maar als je de backticks vergeet dan krijgt delete ineens de betekenis van een DELETE query en dan selecteer je niet meer de waarde van de delete kolom, maar de uitkomst van de query DELETE FROM tabelnaam:

SELECT 
  DELETE
FROM
  tabelnaam

Dat levert één record op met daarin één kolom genaamd delete en daarin de waarde true. Later merk je dan dat je tabel nu leeg is.

Gebruik dus nooit backticks. Geef je tabellen en kolommen een naam die niet gelijk is aan een SQL keyword. Voor elke database kun je in de handleiding een lijst met gereserveeerde woorden vinden die je niet mag gebruiken.

Noot: de meeste databases hebben een soortgelijke functionaliteit, in PostgreSQL werkt het met dubbelquotes. Echter MySQL is de enige die het actief aanraadt als een soort preventieve bugfix.

Selecteer enkel die velden die je nodig hebt

Het is natuurlijk heel makkelijk om gewoon alle velden te selecteren met '*'. Maar heb je deze wel allemaal nodig?

De database verwerkt de gegevens, maar je gebruik ze niet. Dat is verspilling van resources en helemaal nergens voor nodig.

Selecteer altijd ALLEEN de velden die je ook echt daadwerkelijk gebruikt. Zelfs als je alle velden gebruikt kan het handig zijn, deze gewoon allemaal te selecteren bij hun naam. Zodat je weet welke velden er allemaal zijn, en niet steeds in de database (of als je het helemaal goed hebt gedaan, documentatie) moet duiken om ze terug te vinden.

Bovendien krijg je dan ook een waarschuwing "column X not found" als je een kolom in een tabel verwacht die er helemaal niet is.

Aliassen

Een alias is wat de naam zegt; een andere naam voor hetzelfde. In SQL kun je aliassen gebruiken voor kolommen, tabellen en queries.

Aliassen voor overzichtelijkheid

Een alias kan een compleet andere tekst zijn dan hetgeen hij naar verwijst. Een lange of cryptische naam kan worden ge-aliast naar een naam die beter uitlegt wat er in staat.

Vuistregels

Het is zeer verleidelijk om de tabel 'users' af te korten tot 'u'. Maar als je ook een tabel 'userprofiles' hebt dan moet je daar iets anders voor gebruiken en dat wordt meestal 'up'. Als je daarna een tabel 'userprivileges' krijgt dan kan 'up' ook niet meer en ga je over naar 'upv' of iets anders cryptisch.

Het doel van de alias wordt dan zuiver en alleen "zo kort mogelijk zijn" en doet niet meer waarvoor je hem gebruikt: eenvoudig de juiste tabel aanwijzen.

Gebruik daarom liever een alias die de naam van de tabel vereenvoudigt. Kort dingen op een natuurlijke manier af, b.v. door de eerste lettergreep te gebruiken. userprofiles kan userprof worden, dat is duidelijk en toch korter.

Een voorbeeld van hoe het niet moet:

SELECT 
   x.id
   , x.naam
   , y.beroep
FROM 
   tabelmetlangenaamvanuser AS x
 , tabelmetberoepen AS y

Maar liever:

SELECT 
   user.id
   , user.naam
   , job.beroep
FROM 
   tabelmetlangenaamvanuser AS user
   , tabelmetberoepen AS job

Onderhoudbaarheid

Door een alias te gebruiken wordt de naam van en tabel of expressie op één plek ingesteld en in de rest alleen gebruikt. Mocht er een noodzaak zijn om de data waar de alias naar verwijst te veranderen, b.v. omdat het een andere tabel wordt, dan hoeft dat alleen te worden veranderd op de plek waar de alias wordt toegewezen.

Hoe werkt een alias

Een alias kan op twee manieren worden ingezet; als andere naam voor de dingen die je selecteert, of als een andere naam voor de dingen waar je uit selecteert.

Aliassen zijn ook praktisch noodzakelijk als je een berekening selecteert omdat de uitkomst van die berekening anders in de resultset komt te staan onder een naam die gelijk is aan de berekening, en dat is bijzonder onhandig.

SELECT
  simpele_alias.ingewikkelde_kolomnaam AS mijnalias
  , (1+4/6 * veldwaarde) AS uitkomst
  , DATE_FORMAT(datum, '%d-%m-%Y') AS datumgeformatteerd
FROM
  tabel_met_een_ingewikkelde_naam AS simpele_alias
INNER JOIN
  (SELECT * FROM users WHERE role="meester") AS meesterusers
ON
  simpele_alias.userid = meesterusers.userid

Aliassen voor tabellen

Tabellen kunnen ook een alias krijgen. Dit is in elk geval nodig als 1 tabel vaker voorkomt in je query, maar dit kan ook gedaan worden als je lange tabelnamen wilt inkorten omdat je anders op veel plaatsen in je code de lange tabelnaam nodig hebt.