Datan hallintakieli (DML)

Datan hallintakieli (Data Management Language, DML) muodostaa suuren osan SQL-kielestä. Sen avulla tietokannasta voidaan mm. etsiä SELECT, lisätä INSERT, muokata UPDATE ja poistaa DELETE dataa. Toisin sanoin DML-kielellä tietokantaan voi kohdistaa CRUD-operaatioita (Create, Read, Update, Delete).

Seuraavaksi tarkastellaan näitä neljää komentoa.

Esimerkkitietokanta

Seuraavat osiot sisältävät interaktiivisia esimerkkejä. Voit kokeilla ajaa valmiiksi kirjoitettuja SQL-lauseita ja voit myös muokata niitä. Materiaalissa käytettävä tietokannanhallintajärjestelmä on SQLite, joka sopii lokaalien, sovellukseen integroitujen, tietokantojen ylläpitoon.

Kokeile alkuun tietokannan toimivuutta alla olevalla interaktiivisella tehtävällä. Painamalla alla olevan esimerkin Aja-painiketta esimerkkitietokanta ladataan ja tulosteena näytetään kaikki tietokannassa olevien taulujen nimet.

Alla on esitetty interaktiivisissa esimerkeissä käytetyn tietokannan looginen rakenne (myös nimeltään kaava tai skeema).

Voit tarkastella tietokannan luomiseksi käytettyä koodia tästä: Avaa esimerkkitietokannan SQL-lähdekoodia uudessa välilehdessä

Tietokannassa on neljä taulua: asiakas, tuote, lasku ja lasku_rivi. Yllä olevassa kaavassa taulujen nimi on esitetty suorakulmion ensimmäisellä rivillä ja taulun sarakkeiden nimet tietotyyppeineen seuraavilla. Viiteavaimet on esitetty viivoilla. Kunkin taulun rivi kuvastaa seuraavaa tietoa:

• Asiakkaalla on yksilöllinen tunnus (astun), nimi (asnimi), asuinkaupunki (kaup), asiakkuuden tyyppi (tyyppi; 'y' = yritysasiakas, 'h' = henkilöasiakas) ja myyntipiiri (mpiiri; 'i' = itä, 'l' = länsi, 'e' = etelä, 'p' = pohjoinen, 'k' = keski).
• Tuotteella on tunnus (tuotetun), nimi (tuotenimi), malli (malli), yksikköhinta (ahinta) ja väri (vari).
• Laskulla on laskunumero (laskuno), laskutusvuosi (vuosi), laskun yhteissumma (lask_summa), tila (tila; 'm' = maksettu, 'l' = laskutettu, 'k' = karhuttu) sekä asiakas, jota on laskutettu (astun).
• Taulu lasku_rivi kuvaa mitä tuotetta (tuotetun) on laskutettu milläkin laskulla (laskuno) ja kuinka monta kappaletta (maara).

Syntaksimerkinnät

Tässä ja seuraavissa luvuissa SQL-kielen syntaksia esitellään eräänlaisena pseudokielenä, johon on merkitty ylös, mitkä syntaksin osat ovat pakolliset, mitkä ei, ja mitkä osat voidaan toistaa usean kerran.

Käytettävät merkinnät ja niiden merkitykset:

Yhteen tauluun kohdistuvat hakulauseet

Tarkastellaan ensin yhteen tauluun kohdistuvia hakulauseita, hakulauseen yleistä muotoja ja erilaisia ehtolausekkeita.

Hakulauseen yleinen muoto

SQL-hakulause eli SELECT-lause muodostuu yksinkertaisimmillaan seuraavasti:

SELECT-osassa luetellaan pilkkulistalla ne sarakkeet, joiden arvoja halutaan kyselyn tulokseen. Tulostauluun (eli tauluun, jonka saadaan SQL-kyselyn tuloksena) ensimmäiseksi luetellusta sarakkeesta tulee tulostaulun vasemmanpuoleinen sarake, toiseksi toinen, jne. FROM-osassa luetellaan pilkkulistalla ne taulut, joista tietoa etsitään. FROM-osan sisältöä kutsutaan myös taulujen esittelyksi.

SELECT-osassa voidaan myös sarakkeiden nimien sijaan käyttää tähteä * kuvaamaan taulun kaikkia sarakkeita alla olevan esimerkin mukaisesti.

Ehtolausekkeet ja tulosten rajaus

Hakulauseen yleinen muoto SELECT...FROM...; noutaa taulusta kaikki rivit. Kun tuloksia halutaan rajata, käytetään ehtolausekkeita, jotka sijoitetaan lauseen WHERE-osaan. WHERE-osa sijoittuu FROM-osan jälkeen:

Vertailuoperaattorit

Ehtolausekkeet koostuvat usein sarakkeiden arvojen vertailusta. SQL-kielessä yleisesti tunnistamat vertailuoperaattorit ovat:

Huomaa, että SQL-kielessä on kaksi eri merkintätapaa erisuuruudelle: <> sekä !=.

Useita ehtolausekkeita voidaan yhdistää toisiinsa loogisilla operaattoreilla AND (ja) ja OR (tai) sekä edelleen sulkeilla. Jos lauseen WHERE-osassa käytetään vain AND-operaattoreita, ehtolausekkeiden järjestyksellä ei ole tulosten kannalta merkitystä.

Merkkijonojen vertailu

Merkkijonojen vertailuun voidaan käyttää vertailuoperaattoreita tai [NOT] LIKE -predikaattia. Vertailtava merkkijono kirjoitetaan heittomerkkien ' sisään. Kirjainkoolla heittomerkkien sisällä on tavallisesti merkitystä, kuitenkin tuotteesta ja sen asetuksista riippuen. SQLite:ssä oletusarvoisesti kirjainkoolla ei ole merkitystä.

LIKE-predikaattia käytettäessä voidaan käyttää lisäksi seuraavia jokerimerkkejä:

• Alaviiva _ vastaa yhtä mitä tahansa merkkiä.
• Prosenttimerkki % vastaa 0..n kappaletta mitä tahansa merkkiä. Ts. prosenttimerkki vastaa mitä tahansa merkkijonoa (myös tyhjää).

Loogisten operaattorien suoritusjärjestys

Kuten aritmeettiset operaatiot, jotkin operaattorit ovat SQL:ssä etuoikeutetumpia kuin toiset. Esimerkiksi siinä missä tulo lasketaan ennen summaa, SQL:ssä AND tarkastetaan ennen OR-operaattoria. Jos halutaan tarkastaa OR ennen AND-operaattoria, lauseen suoritusjärjestystä voidaan ohjata sulkeilla kuten matematiikassa, eli sulkeiden sisällä olevat asiat suoritetaan ensin. Tarkastellaan seuraavaa esimerkkiä:

Yllä olevassa lauseessa suoritusjärjestys on ohjattu sulkeilla: ensin tarkastetaan, että tuotenimi on yksi halutuista (OR), ja sitten onko hinta yksi halutuista (OR). Lopuksi vielä tarkastetaan, että sekä nimen että hinnan ehdot pätevät (AND).

Yhteenvetona, loogisten operaattoreiden suoritusjärjestys seuraa seuraavaa järjestystä:

1. Oikeasta vasemmalle (tai ylhäältä alas).
2. Suluissa olevat operaattorit ensin.
3. AND
4. OR

Jos sulkeita ei käytettäisi, muuttuisi lauseen logiikka seuraavilla tavoilla:

• Jos jätetään pois kaikki sulkeet, SQL-kyselyn ehto tulkittaisiin seuraavassa järjestyksessä (hakasulkeet [ ] lisätty korostamaan suoritusjärjestystä):

  tuotenimi LIKE 't%'                               -- 2.
  OR [ tuotenimi LIKE 's%' AND ahinta > 200 ]       -- 1.
  OR ahinta < 20                                    -- 3.

  Toisin sanoin hyväksytään sellaiset tuotteet, joiden nimi alkaa t-kirjaimella. Hyväksytään myös sellaiset tuotteet, joiden nimi alkaa s-kirjaimella ja joiden hinta on yli 200. Hyväksytään myös sellaiset tuotteet, joiden hinta on alle 20.

• Jos jätetään pois ainoastaan alemmat sulkeet eli hinnan tarkastukset ympäröivät sulkeet, SQL-kyselyn ehto tulkittaisiin seuraavassa järjestyksessä (hakasulkeet [ ] lisätty korostamaan suoritusjärjestystä):

  [
    (tuotenimi LIKE 't%' OR tuotenimi LIKE 's%')        -- 1.
    AND ahinta > 200                                    -- 2.
  ]
  OR ahinta < 20                                        -- 3.

  Eli hyväksytään sellaiset tuotteet, joiden nimi alkaa t- tai s-kirjaimella, ja joiden hinta on yli 200. Hyväksytään myös sellaiset tuotteet, joiden hinta on alle 20.

• Jos jätetään pois ainoastaan ylemmät sulkeet eli tuotenimen tarkastukset ympäröivät sulkeet, SQL-kyselyn ehto tulkittaisiin seuraavassa järjestyksessä (hakasulkeet [ ] lisätty korostamaan suoritusjärjestystä):

  tuotenimi LIKE 't%' OR                    -- 3.
  [
    tuotenimi LIKE 's%' AND                 -- 2.
    (ahinta > 200 OR ahinta < 20)           -- 1.
  ]

  Silloin hyväksytään sellaiset tuotteet, joiden nimi alkaa t-kirjaimella. Hyväksytään myös sellaiset tuotteet, joiden nimi alkaa s-kirjaimella ja joiden hinta on yli 200 tai alle 20.

Tyhjäarvo ja vertailu

Tyhjäarvo NULL kuvastaa arvoa, jota ei tunneta tai jolla ei ole rivin kannalta merkitystä (ks. Luku 3.1). Jos tyhjäarvoa yritetään vertailla vertailuoperaattoreilla, palautetaan aina tyhjäarvo. Tähän asti ehtolausekkeita on tarkasteltu kaksiarvoisen logiikan mukaisesti, esim. ehtolauseke ahinta > 100 palauttaa joko arvon TRUE (tosi) tai FALSE (epätosi) riippuen siitä, mikä arvo rivin ja sarakkeen leikkauskohdassa on. Tyhjäarvon johdosta SQL toimii kolmiarvoisella logiikalla seuraavan totuustaulun mukaisesti.

SQL:ssä tyhjäarvoa kuvataan avainsanalla NULL. Tyhjäarvon esiintymistä tarkastetaan predikaatilla IS [NOT] NULL, ei koskaan vertailuoperaattorilla.

Muita tapoja vertailuun

Vertailuoperaattoreiden ja LIKE-predikaatin lisäksi SQL-standardissa on muita, tilanteesta riippuen vaivattomampia tapoja vertailuun.

[NOT] IN -predikaatilla voidaan tarkastaa, kuuluuko arvo johonkin joukkoon. Sitä voidaan käyttää esimerkiksi korvaamaan joukon OR-operaattoreita yhdellä operaattorilla. [NOT] IN -predikaatille annetaan hyväksyttävä arvojoukko sulkeiden sisään pilkkulistalla. Huomaa, että jokerimerkkien käyttö ei ole sallittua.

[NOT] BETWEEN -predikaatti tarkastaa, onko sarakkeen arvo halutulla välillä. Syntaksi on sarake BETWEEN arvo1 AND arvo2, jossa arvo1 on pienempi ja arvo2 suurempi. Predikaatilla voidaan vertailla myös merkkijonoja ja päivämääriä. Huomaa, että myös raja-arvot hyväksytään tuloksiin.

Tulosten järjestäminen

Tähän asti käsitellyissä esimerkeissä tulostaulun rivien järjestys on ollut tietokannanhallintajärjestelmän päättämä. Tulostaulun voi järjestää mieleisekseen ORDER BY -määreellä. Se sijoittuu tähän mennessä käsiteltyjen osien jälkeen:

Tulostaulu voidaan järjestää sen kaikkien sarakkeiden mukaan, tai vain osan. Jos ORDER BY -määrettä käytetään, sarakkeen arvot järjestetään oletusarvoisesti nousevaan järjestykseen (ASC eli ascending). Järjestys voidaan kääntää laskevaan järjestykseen lisämääreellä DESC (descending).

Tulosten määrän rajaaminen

Joskus tietokannasta haettavien rivien määrää halutaan rajata. Esimerkiksi jos halutaan selvittää, löytyykö tietokannasta edes yksi ehtoa täyttävä rivi, on turhaa hakea kaikki mahdolliset rivit.

Tulostaulun tulevien rivien lukumäärä voi rajata LIMIT-määreellä. LIMIT-määre tulee aivan kyselyn loppuun kaikkien muiden osien jälkeen:

LIMIT-määre soveltuu monesti hyvin ORDER BY -määreen kanssa silloin, kun halutaan vastata kysymyksiin mallia "Hae N parasta/korkeinta/pisintä...".

Useaan tauluun kohdistuvat hakulauseet

Tähän mennessä käsitellyt hakulauseet ovat kohdistuneet yhteen tauluun kerrallaan. Tietokannoissa on kuitenkin tavallista, että samassa haussa haetaan tietoa useasta taulusta. Toisaalta on myös yleistä, että yhden taulun rivejä halutaan valita toisessa taulussa olevien rivien perusteella.

Tärkein useamman kuin yhden taulun käsittelyyn liittyvä käsite on liitosehto. Liitosehdon avulla tarkastetaan, löytyykö kahdesta eri taulusta sama sarakkeen arvo. Yleensä liitosehdot tehdään viiteavaimella renkitaulun viiteavaimen sekä isäntätaulun viitatun sarakkeen välille (mutta tämä ei ole pakollista, vaan liitos voidaan tehdä minkä tahansa kahden taulun sarakkeen välille). Liitosehdon avulla eri tauluissa olevat rivit voidaan "liimata" (eli liittää) yhteen ja ne palauttaa tulostaulussa.

SQL-kielessä on neljä tapaa toteuttaa liitosehto:

1. Liitos IN-predikaattia käyttäen
2. Liitos EXISTS-predikaattia käyttäen
3. Liitos vertailuoperaattoria käyttäen
4. Liitos JOIN-määreellä

Näistä tavat 1-3. ovat ns. implisiittiset liitokset, jossa tietokannanhallintajärjestelmä automaattisesti tunnistaa, että eri tauluissa olevia rivejä tulee liittää yhteen. Tapa 4. on puolestaan eksplisiittinen liitos, eli kyselyssä pyydetään erikseen liittämään kahta tai useampaa taulua yhteen.

Jokaisella tavalla on hyötynsä ja haasteensa. Seuraavaksi käydään läpi kaikki neljä tapaa liittää eri taulussa olevia rivejä yhteen.

Liitos IN-predikaattia käyttäen

Yksi tapa toteuttaa liitos on ns. alikyselyllä, jolloin lauseen WHERE-osassa aloitetaan uusi, SELECT-käskyllä alkava hakulause. Liitosehto voidaan toteuttaa IN-predikaattia käyttäen. Kiinnitä huomiota siihen, mikä taulu esitellään missäkin FROM-osassa:

Tarkastellaan tarkemmin, mitä yllä olevassa lauseessa tapahtuu. Tietokannan tuote-taulussa on listattuna kaikkien tietokannassa olevien tuotteiden tiedot. Tietokannan lasku_rivi-taulussa on puolestaan listattuna sellaisten tuotteiden tuotetunnukset, joita koskee jokin lasku, eli joista on joskus laskutettu jotakuta asiakasta. Toisin sanoen, tuote-taulussa on tallennettuna kaikki tuotteet, mutta lasku_rivi-taulussa vain laskutettujen tuotteiden tunnuksia.

IN-predikaatista muistamme, että sillä tarkastetaan, kuuluuko vertailtavan sarakkeen arvo johonkin joukkoon (ks. [NOT] IN-vertailu). Tässä IN-predikaatin oikealle puolelle ei olekaan asetettu pilkkulistaa hyväksyttävistä arvoista, vaan toinen hakukysely (ns. alikysely). IN-predikaatin vasemmalla puolella on tuote-taulun tuotetun-sarakkeen arvo, oikealla puolella puolestaan lasku_rivi-taulun tuotetun-sarakkeen arvo.

Kyselyn voisi lukea näin: "Hae sellaisten tuotteiden tuotenimet, joiden tuotetunnus on tallennettu myös lasku_rivi tauluun" tai "Hae sellaisten tuotteiden tuotenimet, joiden tuotetunnus esiintyy ainakin kerran lasku_rivi-taulussa" tai "Hae sellaisten tuotteiden tuotenimet, joista on laskutettu ainakin kerran".

Miten lause sitten suoritetaan? Alikyselyä voisi ajatella kahtena sisäkkäisenä silmukkana imperatiivisessa ohjelmointikielessä:

1. Valitaan tuote-taulun ensimmäiseltä riviltä tuotetun-sarakkeen arvo x.
2. Verrataan x:ää lasku_rivi-taulun ensimmäisen rivin tuotetun-sarakkeen arvoon y.
   • Jos ehtolauseke x = y saa arvokseen TRUE (ts. arvot ovat samat), sijoitetaan tuote-taulun x:ää vastaavan rivin tuotenimi-sarakkeen arvo tulostauluun. Siirrytään kohtaan 3.
   • Jos ehtolauseke x = y saa arvokseen jotakin muuta (FALSE tai NULL), tarkastetaan lasku_rivi-taulun seuraavan rivin sarakkeen tuotetun-arvo, ja verrataan sitä x:ään. Jos lasku_rivi-taulun miltään riviltä ei löydy x:ää vastaavaa arvoa, siirrytään kohtaan 3.
3. Valitaan tuote-taulun seuraavalta riviltä tuotetun-sarakkeen arvo x ja siirrytään kohtaan 2., kunnes tuote-taulun viimeinenkin rivi on tarkastettu.
4. Palautetaan tulostaulu kyselyn tuloksena.

Saman periaatteen mukaan voidaan toteuttaa monimutkaisempiakin kyselyitä. Esimerkiksi alla oleva, luonnollisella kielellä esitetty hakulause voi tietokannan rakenteesta riippuen näyttää SQL:llä esitettynä monimutkaiselta:

Yllä olevan lauseen voisi lukea auki seuraavasti.

"Hae sellaisten asiakkaiden nimet ja tyypit,":

"...joiden asiakastunnus on tallennettu myös lasku-tauluun,":

"...ja vastaavan lasku-taulun rivin laskuno-sarakkeen arvo on tallennettu myös lasku_rivi-tauluun,":

"...ja vastaavan lasku_rivi-taulun rivin tuotetun-sarakkeen arvo on tallennettu myös tuote-tauluun,":

"...ja tuote-taulussa vastaavan rivin tuotteen väri on musta":

Liitos EXISTS-predikaattia käyttäen

Alikyselyyn perustuva liitos voidaan tehdä myös käyttämällä EXISTS-predikaattia. Syntaksi eroaa hieman IN-predikaatista, sillä varsinainen liitosehto tehdään vasta alikyselyn WHERE-osassa. EXISTS-predikaatilla tarkastetaan, onko ehdot täyttäviä rivejä olemassa. Jos alikysely tuottaa totuusarvon TRUE edes yhdelle riville, valitaan pääkyselyssä esitellystä taulusta vastaavan rivin halutun sarakkeen arvo tulostauluun.

Vastaava kysely näyttäisi IN-predikaatilla toteutettuna tältä:

Huomaa erityisesti, että varsinainen liitos tapahtuu eri kohdassa kuin IN-predikaattia käyttäen. Alikyselyt tuovat mukanaan uuden käsitteen: näkyvyysalueen. Näkyvyysalueella tarkoitetaan SQL-kielessä sitä, missä kohdassa lausetta jonkin sarakkeen tai taulun nimeä voidaan käyttää. Alikyselyssä esiteltyihin tauluihin tai niiden sarakkeisiin ei voi viitata ylemmän tason kyselyssä, mutta ylemmän tason kyselyssä esiteltyihin tauluihin ja niiden sarakkeisiin voidaan viitata alikyselyssä.

Yllä olevassa esimerkissä pääkysely ei ole tietoinen lasku_rivi-taulusta, mutta alikysely on tietoinen lasku_rivi-taulun lisäksi pääkyselyssä esitellystä tuote-taulusta.

Näkyvyysalueet ja useamman kuin yhden taulun esittely tuovat mukanaan ongelman: viitattaessa tuotetun-sarakkeeseen tietokannanhallintajärjestelmä ei tiedä, tarkoitetaanko lauseessa tuote- vai lasku_rivi-taulun tuotetun-saraketta. Tästä syystä on käytettävä tarkentimia (correlation name). Tarkentimena voi käyttää taulun nimeä, kuten yllä, tai sen voi esitellä itse lauseen FROM-osassa syntaksilla:

Itse määritelty tarkennin voi olla mikä tahansa nimeämissääntöjä noudattava merkkijono. Itse määritelty tarkennin toimii aliaksena taululla; sillä voi vähentää kirjoitustyötä huomattavasti. Esimerkiksi yllä esitetty esimerkki voitaisiin kirjoittaa myös omia tarkentimia käyttäen:

Yllä olevan esimerkin pääkyselyn SELECT-osassa tarkentimen t käyttö ei ole välttämätöntä, koska tuotenimi-niminen sarake on vain tuote-taulussa. Koska liitosehto tehdään alikyselyn WHERE-osassa, EXISTS-predikaatilla toteutetun alikyselyn SELECT-osan sisällöllä ei ole merkitystä. Tavallisesti käytetään tähtimerkkiä tai yhtä numeroa.

Yllä olevan lauseen voidaan ajatella seuraavasti:

"Hae sellaisten tuotteiden nimet,"

"...joille on olemassa taulussa lasku_rivi ainakin yksi rivi,"

", jonka tuotetun-sarakkeessa on sama arvo kuin taulun tuote `tuotetun-sarakkeessa."

Liitos vertailuoperaattoria käyttäen

Kahden tai useamman taulun liitos voidaan tehdä myös ilman alikyselyä. Yksi tapa liitoksen tekemiseen ilman alikyselyä on vertailuoperaattorin käyttäminen.

Kuten aikaisemmin esitellyissä liitoksissa IN- ja EXISTS-predikaatteja käyttäen, yllä olevassa lauseessa tarkastetaan, vastaako tuote-taulun tuotetun-sarakkeen arvo jotakin lasku_rivi-taulun tuotetun-sarakkeen arvoa.

Huomaa, että toisin kuin EXISTS- ja IN-liitoksissa, tässä tapauksessa kaikki liitettävät taulut mainitaan pääkyselyn FROM-osassa ilman alikyselyjä. Osaa FROM tuote t, lasku_rivi lr on helpointa ajatella siten, että tietokannanhallintajärjestelmä ensin hakee ja liittää kaikki mahdolliset rivivaihtoehdot yhteen:

Sen jälkeen osa WHERE t.tuotetun = lr.tuotetun jättää tulokseen vain ne rivivaihtoehdot, jossa taulun tuote tuotetun-sarakkeen arvo ja taulun lasku_rivi tuotetun-sarakkeen arvo ovat samat:

Tästä tulostaulusta lopuksi palautetaan vain tuotenimi-sarake.

Huomaa, että tässä tavassa nyt voi esiintyä toisteisia arvoja: koska lasku_rivi-taulussa sama tuotetun-sarakkeen arvo voi kertautua ja tässä tapauksessa kertautuu, tulostauluun valitaan samaa tuotetta useaan kertaan. Tässä tapauksessa toisteiset rivit voi poistaa tulostaulusta DISTINCT-lisämääreellä. DISTINCT-lisämääre sijoitetaan lauseen SELECT-osaan heti SELECT-avainsanan jälkeen kuten yllä.

Kuten EXISTS- ja IN-liitoksissa, myös vertailuoperaattoreiden kanssa voidaan kohdistaa liitos useaan tauluun.

Yllä olevan lauseen voidaan ajatella seuraavasti:

"Hae taulujen asiakas, lasku, laskurivi ja tuote kaikki rivit ja yhdistä new yhteen kaikilla mahdollisilla tavoilla;"

"...valitse näistä yhdistetyistä riveistä ne, joilla asiakas-taulun astun ja lasku-taulun astun samat,""

"...ja lasku-taulun laskuno ja lasku_rivi-taulun laskuno samat,"

"...ja lasku_rivi-taulun tuotetun ja tuote-taulun tuotetun samat,"

"...ja joilla tuote-taulun vari on musta."

Liitos JOIN-määrettä käyttäen

JOIN-määre on SQL-standardin kolmannessa versiossa (SQL-92) lisätty ja myöhemmin laajennettu tapa toteuttaa liitoksia. JOIN-liitosta kutsutaan myös eksplisiittiseksi liitokseksi, sillä se määrittää tarkasti, miten tietokannanhallintajärjestelmän tulee toteuttaa liitos taulujen välille. Tässä materiaalissa tarkastetaan yhtä yleistä liitostyyppiä, sisäliitos (inner join). Sisäliitoksen syntaksi on seuraava:

Aikaisemmissa esimerkeissä IN- ja EXISTS-predikaateilla sekä vertailuoperaattoria käyttämällä toteutettu kysely näyttäisi eksplisiittisellä liitoksella toteutettuna seuraavalta:

Sisäliitoksen voi tulkita pitkälti samalla tavalla kuin liitos vertailuoperaattoria käyttäen:

"Hae kaikki tuotteiden nimet tuote-taulusta,"

"...johon on ensin liitetty lasku_rivi-taulun rivit liittämällä taulujen kaikki mahdolliset riviparit ja valitsemalla niistä ne, joilla on tuote-taulun tuotetun-sarake ja lasku_rivi-taulun tuotetun-sarake samanarvoiset."

Uusin SQL-kielen standardi (SQL:2023) määrittää sisäliitoksen lisäksi peräti viisi eksplisiittistä liitostyyppiä:

• Vasen ja oikea ulkoliitos (LEFT OUTER JOIN ja RIGHT OUTER JOIN): Tulostauluun otetaan liitetyt rivit sekä liitosehdon vasemmalta/oikealta puoleiselta taulusta rivejä, joita ei voitu liittää liitosehdolla.
• Ulkoliitos (FULL OUTER JOIN): Vasemman ja oikean ulkoliitoksen yhdiste. Toisin sanoin palauttaa kaikki liitetyt rivit sekä kummankin taulun rivit, joita ei voitu liittää liitosehdolla.
• Ristiliitos (CROSS JOIN): Liittää kummankin taulun kaikki mahdolliset riviparit. Vastaa samaa tulosta kuin FROM taulu1, taulu2.
• Luonnollinen liitos (NATURAL JOIN): Tietokannanhallintajärjestelmä yrittää automaattisesti liittää taulut samannimisten ja -tyyppisten sarakkeiden perusteella.

Näiden liitosten yksityiskohdat jätetään toistaiseksi tämän materiaalin ulkopuolelle. Kiinnostuneet voivat tutustua erilaisiin JOIN-liitoksiin seuraavista lähteistä: [29], [15].

Yhdiste

Yhdisteen UNION avulla voidaan liittää kahden tai useamman hakulauseen tulostaulut toisiinsa. Yhdiste ei ole varsinaisesti liitos, vaan tapa yhdistää erillisten kyselyiden tulokset samaan tulostauluun. Kun yhdistettä käytetään, hakulauseiden tulostauluissa tulee olla yhtä monta saraketta.

Edellisessä esimerkissä on myös esitelty uusi SQL-avainsana AS. Sen avulla voidaan mm. nimetä uudelleen tulostaulun sarakkeita. Yllä olevassa esimerkissä tulostaulun ainoalle sarakkeelle on annettu nimi mallit_ja_tuotenimet. AS-predikaatti on käyttökelpoinen erityisesti, kun tulostaulussa on koostefunktioiden tuottamia sarakkeita. Koostefunktioita käsitellään seuraavaksi.

Koostefunktiot

SQL-kielellä on mahdollista suorittaa yksinkertaisia tilastollisia laskuja. Tämä on usein hyödyllistä, kun tietokannassa olevasta datasta halutaan luoda raportteja tai muuten analysoida tietoa hakematta sitä turhaan sovellukseen. SQL-kieli tarjoaa kaksi ominaisuutta analytiikalle: koostefunktiot ja ryhmittely.

Koostefunktioita (set tai aggregate function) käytetään laskutoimitusten suorittamiseen. Niille annetaan tavallisesti yksi parametri ja ne palauttavat yhden arvon. Koostefunktiot sijoitetaan hakulauseessa SELECT- tai HAVING-osaan. HAVING esitellään myöhemmin. Seuraavaksi esitellään tavallisimmat koostefunktiot summa, lukumäärä, minimi, maksimi ja keskiarvo.

Summa ja lukumäärä

Koostefunktio summa SUM laskee ja palauttaa sarakkeessa esiintyvien arvojen summan. SUM käsittelee tyhjäarvoa NULL kuten nollaa, ts. 1 + 0 + 3 = 4 ja 1 + NULL + 3 = 4. Seuraavassa esimerkissä koostefunktiolle on annettu parametriksi tuote-taulun ahinta-sarake, ja tulostaulun ainoa sarake on nimetty AS-predikaatilla. Jos saraketta ei nimetä AS-predikaatilla, tietokannanhallintajärjestelmä antaa sarakkeella automaattisesti jonkun nimen.

Koostefunktio lukumäärä COUNT laskee ja palauttaa arvojen tai rivien lukumäärän. COUNT ei laske tyhjäarvoja. Jos COUNT-funktion parametriksi annetaan sarakkeen nimi, laskee se sarakkeessa olevien arvojen lukumäärän. Jos sen sijaan parametriksi annetaan tähti *, COUNT laskee tulostaulussa olevien rivien lukumäärän.

Seuraavassa esimerkissä on laskettu asiakas-taulun rivien lukumäärä käyttämällä koostefunktion parametrina tähteä.

Silloin tällöin pelkkä arvojen esiintymien lukumäärän laskeminen ei tuota haluttua tulosta, sillä oletusarvoisesti COUNT-koostefunktio laskee arvot riippumatta siitä, mikä arvo on. Jos halutaan laskea erilaisten arvojen määrä, voidaan käyttää DISTINCT-lisämäärettä.

Yllä oleva esimerkki laskee toisin sanoen asiakas-taulun kaup-sarakkeessa esiintyvien erilaisten arvojen lukumäärän.

Minimi, maksimi ja keskiarvo

Koostefunktio minimi MIN palauttaa sarakkeessa esiintyvän pienimmän arvon, koostefunktio maksimi MAX puolestaan suurimman. Koostefunktioiden arvoja voi myös laskea yhteen tavallisilla laskuoperaattoreilla +, -, / tai *. Seuraavassa esimerkissä on laskettu koostefunktioiden palauttamien arvojen erotus.

Koostefunktio keskiarvo AVG laskee sarakkeen arvojen keskiarvon. Koostefunktio AVG laskee summan kuten SUM-koostefunktio, lukumäärän kuten COUNT koostefunktio ja palauttaa näiden osamäärän.

Ryhmittely

Edellisen esimerkin SQL-kysely siis palauttaa kaikkien tuotteiden hintojen keskiarvon. Usein koostefunktioita halutaan kuitenkin käyttää monimutkaisempiin laskutoimituksiin, esimerkiksi tuotteiden hintakeskiarvojen laskemiseen tuoteväreittäin, eli keskiarvo jokaista väriä kohden. Tällöin tarvitaan ryhmittelyä, joka tapahtuu GROUP BY -määreellä:

Sarakkeita, joiden perusteella taulun rivit ryhmitellään, kutsutaan ryhmitteleväksi sarakkeeksi.

Yllä olevan esimerkin mukaisesti tietokannanhallintajärjestelmä jakaa ensin tuote-taulun rivit ryhmiksi vari-sarakkeen arvojen perusteella. Kukin ryhmä koostuu riveistä, joiden vari-sarakkeen arvo on sama. Esimerkiksi ensimmäiseen ryhmään kuuluvat rivit, jotka kuvaavat sinisiä tuotteita, toiseen ryhmään rivit, jotka kuvaavat punaisia tuotteita jne. Lopuksi lasketaan hintakeskiarvo erikseen jokaiselle ryhmälle ja palautetaan ne tulostauluna.

Ryhmitteleviä sarakkeita voi olla useampikin kuin yksi. GROUP BY -määreen käyttö vaaditaan, jos ainakin yksi tulostaulun sarake muodostetaan koostefunktiolla, ja ainakin yksi sarake ilman koostefunktiota. Tässä tapauksessa ryhmittely pitäisi tehdä jokaisen ryhmittelevän sarakkeen mukaisesti. GROUP BY -määre sijoittuu SQL-hakulauseessa heti lauseen WHERE-osan jälkeen. SQL-hakulauseen yleinen syntaksi näyttää siis tähän mennessä seuraavalta:

Rajaaminen ja koostefunktion

Silloin tällöin ryhmiteltyjä tuloksia täytyy rajata. WHERE-osa suoritetaan ennen ryhmittelyä, eikä WHERE-osassa siksi saa käyttää koostefunktioita.

Silloin, kun rivit halutaan rajoittaa ryhmissä tai koostefunktioiden perusteella, voidaan käyttää HAVING-predikaattia. HAVING-osa sijoittuu lauseessa GROUP BY -osan jälkeen, mutta kuitenkin ennen mahdollista ORDER BY -osaa:

HAVING-osa toimii näennäisesti hieman samalla tavalla kuin aikaisemmin käsitelty WHERE-osa: siihen sijoitettujen ehtolausekkeiden avulla voidaan rajata kyselyn tuloksia. HAVING-osa eroaa kuitenkin WHERE-osasta seuraavilta osin:

• HAVING-osaan voidaan sijoittaa koostefunktioita, toisin kuin WHERE-osaan.
• HAVING-osa suoritetaan vasta ryhmittelyn jälkeen, kun taas WHERE-osa suoritetaan ennen ryhmittelyä.
• Edelliseen liittyen, HAVING-osa vaatii aina GROUP BY-osan.

Tyypillisiä ongelmia ja "kikkoja"

SQL-kieli on ilmaisuvoimaltaan varsin tehokas, mutta valitettavasti kaikkia hakukyselyjä ei ole helppoa muodostaa. Tarkastellaan siis ratkaisuja tyypillisiin hakuongelmiin, jotka vaikuttavat luonnollisella kielellä yksinkertaisilta.

"Ei ole olemassa" -kysely

Silloin tällöin tuloksiin halutaan taulun sellaiset rivit, joihin viittaavia arvoja ei löydy jostakin muusta taulusta. Vertailuun voidaan käyttää NOT IN- tai NOT EXISTS -predikaattia:

NOT IN-predikaatti tarkastaa, onko yhtään vastaavaa riviä olemassa. Jos yksikin rivi täyttää alikyselyn ehdot (yllä olevassa esimerkissä siis sekä liitosehto että sisällöllinen ehto vuosi = 2011), ei pääkyselyn vastaavaa riviä hyväksytä tulostauluun. Yllä oleva lause voitaisiin lukea myös näin: "Hae sellaisten asiakkaiden asiakastunnukset ja nimet, joiden asiakastunnus ei ole lasku-taulussa yhdelläkään sellaisella rivillä, jossa laskun vuosi on 2011".

Sama kysely voidaan toteuttaa myös NOT EXISTS-predikaatilla. NOT EXISTS-predikaatin toiminta noudattaa kaksiarvoista (TRUE, FALSE) logiikkaa, ja tyhjäarvon vertailu tuottaa aina totuusarvon epätosi. Lauseiden syntaksi eroaa samoin kuin IN- ja EXISTS-predikaattien:

On syytä huomata, että ei ole olemassa -tapausta ei voida esittää ilman alikyselyä, ns. yksitasoisesti.

Alla on esitetty yleinen virhe ei ole olemassa -tapauksen käsittelystä.

Yllä oleva lause ei siis vastaa vaatimukseen "Hae sellaisten asiakkaiden asiakastunnukset ja nimet, joita ei ole koskaan laskutettu vuonna 2011." vaan vaatimukseen "Hae sellaisten asiakkaiden asiakastunnukset ja nimet, joita on laskutettu ainakin kerran jonakin muuna vuonna kuin 2011".

Alikyselyn tulosten vertailu vakioon

Alikyselyn tuloksia voidaan vertailla vakioon vertailuoperaattoria käyttämällä.

Yllä olevan esimerkin voisi lukea näin: "Hae niiden lasku_rivi-taulun laskujen numerot, joita koskevan tuotteen tuotetunnus löytyy myös tuote-taulusta ja tämän tuotteen yksikköhinta on alle 10 euroa."

Seuraavassa esimerkissä vakiota 2 verrataan alikyselyn tuloksiin. Alikyselyn tulos on koostefunktion palauttama luku.

Tietokannanhallintajärjestelmä voisi suorittaa yllä olevan esimerkkilauseen seuraavalla tavalla.

1. Valitaan asiakas-taulun ensimmäinen rivi asiakas1.
2. Valitaan lasku-taulun ensimmäinen rivi lasku1.
3. Verrataan lasku1:n ja asiakas1:n astun-sarakkeen arvoja toisiinsa.
   • Jos ehtolauseke a.astun = l.astun saa arvokseen tosi, lisätään koostefunktion palauttamaa arvoa yhdellä ja siirrytään vertailemaan asiakas1:n astun-sarakkeen arvoa lasku1:n seuraavan rivin astun-sarakkeen arvoon.
   • Jos ehtolauseke saa arvokseen epätosi tai tuntematon, siirrytään vertailemaan asiakas1:n astun-sarakkeen arvoa lasku1:n seuraavan rivin astun-sarakkeen arvoon.
   • Kun kaikki asiakas1-rivin astun-sarakkeen arvoa on verrattu kaikkiin lasku-taulun astun-sarakkeen arvoihin, siirrytään kohtaan 4.
4. Tarkastetaan ehtolauseke, jonka vasemmalla puolella on vakio 2 ja oikealla puolella koostefunktion COUNT palauttama kokonaisluku.
   • Jos ehtolauseke on tosi, hyväksytään asiakas1-rivin halutut sarakkeet (eli asnimi, kaup) tulostauluun.
   • Jos ehtolauseke on epätosi, hylätään asiakas1-rivi.
5. Valitaan asiakas-taulun seuraava rivi tarkasteltavaksi.
   • Jos asiakas-taulussa on rivejä tarkastelematta, siirrytään kohtaan 2.
   • Jos asiakas-taulussa ei ole rivejä tarkastelematta, siirrytään kohtaan 6.
6. Palautetaan tulostaulu.

Alikyselyn tuloksia voidaan verrata myös sarakkeeseen. Seuraavassa esimerkissä alikyselyn tuloksia verrataan sarakkeeseen ahinta.

Saman taulun usea läpikäynti

Läpikäynnillä tarkoitetaan tässä yhteydessä taulun esittelyä hakulauseen FROM-osassa. Jos taulu halutaan tarkastaa useammin kuin kerran, on käytettävä apuna joko alikyselyiden mahdollistamia näkyvyysalueita tai useita tarkentimia.

Tietokannanhallintajärjestelmä voisi suorittaa yllä olevan hakulauseen esim. seuravaalla tavalla:

1. Hylätään kaikki asiakas-taulun rivit, joilla asnimi-sarakkeen arvo ei ole Kajo (alikyselyn asiakas-taulun läpikäynti).
2. Hylätään kaikki asiakas-taulun rivit, joilla asnimi-sarakkeen arvo on Kajo (pääkyselyn asiakas-taulun läpikäynti).
3. Verrataan kahden läpikäynnin tuloksia (ts. niitä rivejä, joita ei ole hylätty) käyttäen liitosehtona mpiiri-sarakkeen arvoja. Ne rivit, joilla mpiiri on sama molempien läpikäyntien tuloksissa, valitaan pääkyselyn asiakas-taulusta asnimi- ja mpiiri-sarakkeiden arvot tulostauluun.
4. Palautetaan tulostaulu.

Sama voidaan saavuttaa myös yksitasoisella ratkaisulla käyttämällä eri läpikäynneille eri tarkentimia. Alla olevassa esimerkissä tarkennin a1 vastaa yllä olevan esimerkin pääkyselyä ja tarkennin a2 alikyselyä:

"Etsi X, jolla on kaikki Y" -tapaus

Toisinaan voi tulla tapauksia, jossa halutaan tunnistaa löytyykö liitosehdon muodostavan sarakkeen arvo liitoksen toisen puolen taulun jokaiselta riviltä. Yleiset esimerkit tällaisista ongelmista ovat:

• Hae asiakkaat, jotka ovat tilanneet kaikki diesel-malliset tuotteet.
• Hae sellaiset laskut, joissa on tilattu kaikkia tuotteita.
• Hae tuotteet, joita kaikki asiakkaat ovat tilanneet.

Tällaista ongelmaa voi ratkaista joko NOT EXISTS-predikaatilla tai koostefunktioiden avulla. Alla esitellään tutkitusti selkeämpi ja suorituskyvyltään tasaisempi vaihtoehto koostefunktioiden avulla [26].

Yllä oleva kysely vertaa laskunumeroittain lasku_rivi-taulun rivien määrää (ts. laskua koskevien erilaisten tuotetunnusten määrää) kaikkien tuotteiden lukumäärään.

Taulurivien lisääminen

Taulurivi lisätään komennolla INSERT. Komento lisää rivin tai rivejä yhteen tauluun. Komennon syntaksi on:

Alla olevassa esimerkissä lisätään tauluun asiakas uusi rivi. Rivin sarakkeiden nimet luetellaan INTO-osassa, ja VALUES-osassa määritetään uudelle riville sen sarakkeiden arvot. Sarakkeiden nimet voidaan luetella missä järjestyksessä tahansa, mutta VALUES-osan listan arvot asetetaan siinä järjestyksessä, kuin sarakkeiden nimet on INTO-osassa lueteltu.

Sarakkeiden nimien listauksesta voidaan jättää sarakkeita pois. Tällöin tietokannanhallintajärjestelmä asettaa luettelemattomien sarakkeiden arvoksi tyhjäarvon, oletusarvon tai jonkin ennalta määräämän arvon. Sarakkeiden nimiä täytyy olla INTO-osassa lueteltuna yhtä monta kuin VALUES-listassa on sarakkeita. Poikkeuksena tähän sääntöön sarakkeiden nimien listaus voidaan jättää kokonaan pois, jolloin VALUES-osan listassa täytyy olla yhtä monta arvoa kuin taulussa on sarakkeita. Arvot listataan tässä tapauksessa siinä järjestyksessä kuin sarakkeet ovat taulussa.

Sarakkeelle voidaan antaa arvo myös alikyselyn tuloksiin perustuen. Alikyselyn täytyy tällöin palauttaa ainoastaan yksi arvo, joka voi olla myös tyhjäarvo. Lisäyslauseen syntaksi muuttuu tässä tapauksessa niin, että vakion sijaan pilkkulistalla esitetään hakulause:

Joissakin tietokannanhallintajärjestelmissä (esim. PostgreSQL) voidaan yhdellä INSERT-lauseella lisätä useita rivejä seuraavan syntaksin mukaisesti:

Taulurivejä voidaan myös lisätä toisesta taulusta, ts. uudet rivit voivat perustua aiemmin tietokantaan tallennettuun dataan. Tällöin komennon syntaksi on seuraava:

Myös tässä tapauksessa on syytä huomata, että INTO-osassa täytyy olla lueteltuna yhtä monta saraketta kuin lähdetaulusta valitaan SELECT-osassa. Hakulauseeseen voidaan asettaa miten monimutkaisia ehtoja vain: sarakkeiden arvojen tarkistuksia, alikyselyitä, koostefunktioita jne.

Taulurivien muokkaaminen

Yhden taulun sarakkeiden arvoja voidaan muuttaa komennolla UPDATE. Komennon syntaksi on:

Jossa SET-osassa määrätään, minkä sarakkeiden arvoja muokataan ja miten, ja WHERE-osassa määrätään, minkä rivien osalta sarakkeiden arvoja muokataan.

Myös lausekkeeseen voidaan asettaa alikysely. Seuraavassa asetetaan sellaisten tuotteiden, joiden väriä ei ole määritelty, hinta samaksi kuin halvimman tuotteen hinta. SET-osan alikyselyssä tai WHERE-osassa voidaan asettaa miten monimutkaisia ehtoja vain.

Taulurivien poistaminen

Taulusta voidaan poistaa rivejä komennolla DELETE. Kuten INSERT- ja UPDATE-komennot, DELETE vaikuttaa vain yhden taulun riveihin. Komennon syntaksi on:

Esimerkiksi:

Lauseen WHERE-osassa voidaan asettaa miten monimutkaisia ehtoja vain. Jos WHERE-osa jätetään pois, poistetaan kaikki rivit. DELETE-lause ei varsinaisesti poista rivejä, vaan asettaa ne ylikirjoitettaviksi. Hakulause tauluun ei näytä DELETE-käskyllä poistettuja rivejä, mutta joutuu kuitenkin lukemaan ne.