Select Star SQL
Nederlands

Hiaten

Deze grafiek toont de executies door de tijd heen. Merk op dat er meerdere lange periodes zijn geweest waarin geen executies plaatsvonden. Ons doel is om uit te zoeken wanneer dat precies was en de oorzaken ervan te onderzoeken.

Onze strategie is om de tabel in een vorm te krijgen waarin elke rij ook de datum van de executie daarvoor bevat. We kunnen dan het tijdsverschil tussen de twee datums berekenen, ze in aflopende volgorde sorteren en de langste hiaten aflezen.


Nadenken over joins

Geen van de technieken die we tot nu toe hebben geleerd volstaat hier. De tabel die we willen hebben, is even lang als de oorspronkelijke tabel executions, dus we kunnen aggregaties uitsluiten, omdat die een kleinere tabel opleveren. Het hoofdstuk over Beazley leerde ons alleen bewerkingen op rijen, en die beperken ons tot informatie die al in de rijen staat. De datum van de vorige executie ligt echter buiten de rij, dus we moeten JOIN gebruiken om de extra informatie binnen te halen.

Stel dat de extra informatie die we willen hebben in een tabel met de naam previous staat, met twee kolommen (ex_number, last_ex_date). Dan zouden we de volgende query kunnen uitvoeren om onze taak te voltooien:

SELECT
  last_ex_date AS start,
  ex_date AS end,
  ex_date - last_ex_date AS day_difference
FROM executions
JOIN previous
  ON executions.ex_number = previous.ex_number
ORDER BY day_difference DESC
LIMIT 10

Het JOIN-blok staat centraal in dit hoofdstuk. In plaats van het als een losse regel te zien, is het vaak handig om er zo naar te kijken: Dit benadrukt hoe JOIN een grote gecombineerde tabel maakt, die vervolgens net als elke andere tabel aan het FROM-blok wordt doorgegeven.

Kolommen ondubbelzinnig maken

De bovenstaande query is ook opmerkelijk omdat de voorwaarde executions.ex_number = previous.ex_number de vorm <tabel>.<kolom> gebruikt om kolommen aan te duiden. Dat is hier alleen nodig omdat beide tabellen een kolom met de naam ex_number hebben.


Soorten joins

Het JOIN-blok heeft de vorm <tabel1> JOIN <tabel2> ON <voorwaarde>. De voorwaarde werkt op dezelfde manier als in WHERE <voorwaarde>. Dat wil zeggen: het is een bewering die als waar of onwaar wordt geëvalueerd, en telkens wanneer een rij uit de eerste tabel en een rij uit de tweede tabel de voorwaarde waar maken, worden de twee aan elkaar gekoppeld:

Maar wat gebeurt er met rijen waarvoor geen overeenkomst is? In dit geval had de tabel previous geen rij voor executie nummer 1, omdat er geen executies aan voorafgingen.

Het commando JOIN voert standaard een zogenaamde “inner join” uit, waarbij rijen zonder overeenkomst worden weggelaten.

Om alle rijen van de linkertabel te behouden, gebruiken we een LEFT JOIN in plaats van de gewone JOIN. De lege delen van de rij worden met rust gelaten, wat betekent dat ze de waarde NULL krijgen.

Met RIGHT JOIN kun je rijen zonder overeenkomst in de rechtertabel behouden, en met OUTER JOIN rijen zonder overeenkomst in beide tabellen.

De laatste subtiliteit is hoe meerdere overeenkomsten worden afgehandeld. Stel dat we een tabel duplicated_previous hebben die van elke rij van de tabel previous twee kopieën bevat. Elke rij van executions komt nu overeen met twee rijen in duplicated_previous. De join maakt genoeg rijen van executions aan, zodat elke overeenkomende rij van duplicated_previous een eigen partner krijgt. Op deze manier kunnen joins tabellen opleveren die groter zijn dan de tabellen waaruit ze bestaan.


Datums

Laten we de joins even laten rusten en kijken naar deze regel in onze sjabloonquery:

  ex_date - last_ex_date AS day_difference

We zijn er zomaar van uitgegaan dat we datums van elkaar kunnen aftrekken. Maar stel je voor dat jij de computer bent die een regel als deze ontvangt. Geef je dan het aantal dagen tussen de datums terug? Waarom geen uren of seconden? Om het nog erger te maken, heeft SQLite eigenlijk geen datum- of tijdtypes (in tegenstelling tot de meeste andere SQL-dialecten), dus de kolommen ex_date en last_ex_date zien er voor jou uit als gewone tekenreeksen. Je wordt in feite gevraagd om 'hello' - 'world' uit te rekenen. Wat zou dat überhaupt moeten betekenen?

Gelukkig bevat SQLite een heleboel functies waarmee je de computer kunt vertellen: “Hé, deze tekenreeksen die ik je geef, bevatten eigenlijk datums of tijden. Behandel ze zoals je een datum zou behandelen.”


Self joins

Met wat we over datums hebben geleerd, kunnen we onze sjabloonquery verbeteren:

SELECT
  last_ex_date AS start,
  ex_date AS end,
  JULIANDAY(ex_date) - JULIANDAY(last_ex_date)
    AS day_difference
FROM executions
JOIN previous
  ON executions.ex_number = previous.ex_number
ORDER BY day_difference DESC
LIMIT 5

De volgende stap is het opbouwen van de tabel previous.

Nu kunnen we deze query nesten in ons sjabloon hierboven:

previous is afgeleid van executions, dus in feite koppelen we executions aan zichzelf. Dit heet een “self join” en is een krachtige techniek waarmee rijen informatie kunnen halen uit andere delen van dezelfde tabel.

We hebben de tabel previous gemaakt om duidelijk te maken welk doel hij dient. Maar we kunnen de query eigenlijk eleganter schrijven door de tabel executions rechtstreeks aan zichzelf te koppelen.

We kunnen nu de precieze datums van de hiaten gebruiken om te onderzoeken wat er in elke periode is gebeurd. In de jaren direct nadat het verbod op de doodstraf was opgeheven, waren er lange periodes zonder executies, door het lage aantal doodvonnissen en de juridische aanvechtingen van de nieuwe uitspraak. We laten de onderbrekingen van vóór 1993 daarom buiten beschouwing en richten ons op de twee grote hiaten sindsdien.

Hiaat 1 werd veroorzaakt door juridische aanvechtingen van de Antiterrorism and Effective Death Penalty Act van 1996, die werd ingevoerd als reactie op de bomaanslagen op het World Trade Center in 1993 en in Oklahoma City in 1995. De wet beperkte de beroepsmogelijkheden om de doodstraf effectiever te maken, vooral in terrorismezaken (Bron).

Hiaat 2 werd veroorzaakt door een opschorting die het Hooggerechtshof instelde terwijl het zich boog over Baze v. Rees, een zaak waarin werd onderzocht of de dodelijke injectie in strijd is met het Achtste Amendement, dat “wrede en ongebruikelijke straffen” verbiedt. Dit had gevolgen voor executies in heel Amerika, omdat de meeste staten dezelfde medicijncocktail gebruikten als Kentucky. Het Hooggerechtshof bevestigde uiteindelijk de uitspraak van de rechtbank in Kentucky, en een paar maanden later werden de executies in Texas hervat.


Samenvatting

Het grote idee achter JOINs is het maken van een uitgebreide tabel, omdat de oorspronkelijke tabel niet de informatie bevatte die we nodig hadden. Dit is een krachtig concept, omdat het ons bevrijdt van de beperkingen van één enkele tabel en ons in staat stelt meerdere tabellen op mogelijk complexe manieren te combineren. We hebben ook gezien dat met deze extra complexiteit een nauwgezette boekhouding belangrijk wordt. Tabellen een alias geven, kolommen hernoemen en goede JOIN ON-voorwaarden opstellen zijn allemaal technieken die ons helpen de orde te bewaren.