Keskustelu: Ohjelmointikysymykset: SQL JOIN ja NOT EXISTS

Olen eräällä tietokantakurssilla ja nyt pitäisi opetella tuota EXISTS:n käyttöä.
Tehtävänä oli hakea niiden yli 100 euroa maksavien tuotteiden nimet, joista ei ole yhtään laskua vuodelta 2011.

Pelkistettynä relaatiot ovat tällaiset:
TUOTE(tuotetun, tuotenimi, ahinta)
LASKU_RIVI(tuotetun, maara, laskuno)
LASKU(laskuno, vuosi)

En saanut tällä haulla oikeaa tulosta ulos,

SELECT tuotenimi
FROM tuote
WHERE ahinta > 100 AND
      NOT EXISTS
      (SELECT *
       FROM lasku_rivi
       WHERE laskuno IN
             (SELECT laskuno
              FROM lasku
              WHERE vuosi = 2011
             )
      );

joten koetin näillä kahdella haulla katsoa mitä kaikkea tietokannasta löytyy

SELECT t.tuotetun, t.tuotenimi, t.ahinta, l.vuosi
FROM tuote t
LEFT JOIN lasku_rivi lr, lasku l ON t.tuotetun = lr.tuotetun AND lr.laskuno = l.laskuno AND l.vuosi = 2011
ORDER BY t.tuotenimi, l.vuosi;

SELECT t.tuotetun, t.tuotenimi, t.ahinta, l.vuosi
FROM tuote t, lasku l
WHERE lr.laskuno = l.laskuno AND l.vuosi = 2011
LEFT JOIN lasku_rivi lr ON t.tuotetun = lr.tuotetun
ORDER BY t.tuotenimi, l.vuosi;

Kumpikaan noista ei kuitenkaan toiminut. En ole täysin sinut tämän JOIN:n kanssa miten sitä käytetään eikä meille ole luennoilla tätä opetettu. Enkö voi määritellä JOIN:n sisällä kahta relaatiota, kun ohjelma antoi minulle virheen, joka kohdistuu noiden relaatioiden välissä olevaan pilkkuun? Ja voiko samassa kyselyssä olla sekä WHERE että JOIN? Miten saisin tietokannasta ulos kaikki tuotteet, joista on lasku vuodelta 2011 sekä ne tuotteet, joista ei laskua ole kyseiseltä vuodelta? Eli kaikki tuotteet, niin että vuosi-kohdassa on vuosi 2011 tai NULL sen mukaan löytyykö kyseiseltä vuodelta laskua vai ei. Entä mikähän tuossa alkuperäisessä NOT EXISTS -ratkaisussa menee väärin?

Itse hakisin myös LEFT JOIN kyselyllä.

Jos haluat yhdistää useamman kuin 2 taulua, niin jokaisen taulun väliin tarvii laittaa LEFT JOIN. Eli esim:

SELECT t.tuotetun, t.tuotenimi, t.ahinta
FROM
	tuote t
	LEFT JOIN lasku_rivi lr ON t.tuotetun = lr.tuotetun
	LEFT JOIN lasku l ON lr.laskuno = l.laskuno AND l.vuosi = 2011
WHERE l.laskuno IS NULL
ORDER BY t.tuotenimi

Eli tuossa siis haetaan tuotteet ja kaikki niihin liittyvät laskut vuodelta 2011. Lasku-tauluun liittyvät saarakkeet ovat null, jos ko. tuotteelle ei ole laskuja. Siksi kyselyssä "WHERE l.laskuno IS NULL" hakee ne tuotteet, joille ei ole laskuja vuonna 2011.
Jätin ORDER BY:stä pois tuon l.vuosi, koska se on aina null, eikä näin ollen vaikuta järjestykseen kuitenkaan.

Kiitos Grez. Selitys auttoi huomattavasti ymmärtämään mitä tuo oikein tekee. Tulos ei ollut aivan haluttu, sillä tuo antoi tieten ainoastaan ne tuote-lasku_rivi-lasku riviparit, joissa laskun vuosi ei ollut 2011. Halusin myös nuo vuonna 2011 annetut laskut ja 'lasku' sarakkeessa olisi ollut vuosi 2011, jos lasku olisi ollut vuodelta 2011 ja NULL jos ei olisi ollut. Sain haluamani ottamalla tuon WHERE osan pois, mutta silloin tuotteissa oli aika paljon toistoa niiden laskujen osalta, jotka eivät olleet vuodelta 2011. Korjasin ryhmittelemällä.

SELECT t.tuotetun, t.tuotenimi, t.ahinta, l.vuosi
FROM
	tuote t
	LEFT JOIN lasku_rivi lr ON t.tuotetun = lr.tuotetun
	LEFT JOIN lasku l ON lr.laskuno = l.laskuno AND l.vuosi = 2011
GROUP BY t.tuotetun, l.vuosi, t.tuotenimi, t.ahinta
ORDER BY t.tuotenimi

Nyt minulla voi sama tuote olla siis enintään kahdesti, joista toinen rivi kertoo, että siitä on olemassa lasku vuodelta 2011 ja toinen, että siitä on olemassa lasku(ja) muilta vuosilta kuin 2011. Hiukan jäi kyllä epäselväksi miksi tuo meni läpi ainoastaan silloin kun käytin kaikkia SELECT kohdassa olevia attribuutteja ryhmittely-ehtona. Sain virhettä, jos ryhmittelin ainoastaan tuotenimen ja laskun vuoden mukaan, vaikka aina tuotenimen ollessa sama myös tuotetunnus ja ahinta on sama eli ryhmittelyn ehdot pitäisi täyttyä pelkällä tuotenimellä ja laskun vuodella.

Mutta sitten on vielä auki, että mitä virhettä minä teen tuossa NOT EXISTS kyselyssä?

Edit.
Niin, tosiaan alkuperäinen kysymys oli, että piti hakea tuotteet, joista ei ole laskuja vuodelta 2011. Eli tämä Grez:n antama kyselyhän taitaa melkein vastata siihen? Pitää vain ryhmitellä tuotenimen mukaan, jottei tule samoja useasti ja lisätä ehto, että ahinta on yli 100.

SELECT t.tuotenimi
FROM
	tuote t
	LEFT JOIN lasku_rivi lr ON t.tuotetun = lr.tuotetun
	LEFT JOIN lasku l ON lr.laskuno = l.laskuno AND l.vuosi = 2011
WHERE l.laskuno IS NULL AND t.ahinta > 100
GROUP BY t.tuotenimi
ORDER BY t.tuotenimi

Jostain syystä tämä ei kuitenkaan täysin vastannut kysymykseen vaan palautti liikaa rivejä.

tuli mukaan tällainen rivi, jonka ei olisi pitänyt tulla

tuotetun | tuotenimi | ahinta
t136     | kellotin  | 2000

ja tälle löytyy tietokannasta laskuja vuosilta 2010, 2011 ja 2x2015

No, jokatapauksessa meille ei ole vielä opetettu JOIN:n käyttöä, joten periaatteessa minun ei pitäisi vielä osata sitä eli tämä on tarkoitettu siis NOT EXISTS:n avulla tehtäväksi.

Lisäys:

Kun perehdyin syvemmin tähän kyselyyn, niin huomasinhan minä lopulta miten saan halutun kyselyn NOT EXISTS:n avulla. Olipa typerä virhe. :)

SELECT tuotenimi
FROM tuote t
WHERE ahinta > 100 AND
      NOT EXISTS
      (SELECT *
       FROM lasku_rivi lr
       WHERE t.tuotetun = lr.tuotetun AND
             laskuno IN
             (SELECT laskuno
              FROM lasku
              WHERE vuosi = 2011
             )
      );

Koetin löytää ratkaisua tähän myös jollain yksitasoisella kyselyllä ja päädyin tällaiseen, joka antaa samalla tavalla väärän vastauksen kuin tuo aiemmin tässä viestissä mainitsemani LEFT JOIN -tapaus.

SELECT t1.tuotenimi
FROM tuote t1, tuote t2, lasku_rivi lr, lasku l
WHERE t1.ahinta > 100 AND
      t1.tuotetun <> t2.tuotetun AND
      t2.tuotetun = lr.tuotetun AND
      lr.laskuno = l.laskuno AND
      l.vuosi = 2011
GROUP BY t1.tuotenimi

Asiaa pohdittuani ymmärsin kyllä miksi tämä palauttaa väärän vastauksen, mutta en keksi miten saisin oikean tuloksen. Onkohan sitä edes mahdollista saada tällä yksitasoisella liitoksella?

AkeMake kirjoitti:

Eli tämä Grez:n antama kyselyhän taitaa melkein vastata siihen? Pitää vain ryhmitellä tuotenimen mukaan, jottei tule samoja useasti ja lisätä ehto, että ahinta on yli 100.

Joo, unohdin tuon hinta-ehdon ja sitten tuo että tuotteita tulee liikaa olisi itse asiassa ehkä järkevämpää korjata muuttamaalla toinen JOINi tavalliseksi:

SELECT t.tuotetun, t.tuotenimi, t.ahinta
FROM
	tuote t
	LEFT JOIN (lasku_rivi lr JOIN lasku l ON lr.laskuno = l.laskuno AND l.vuosi = 2011)
		ON t.tuotetun = lr.tuotetun
WHERE lr.tuotetun IS NULL AND t.ahinta > 100
ORDER BY t.tuotenimi

Nyt tuo meni kyllä liian vaikeaksi minulle ymmärtää ja kun koetan tuota hakua, niin tulee virheilmoitus: 'ERROR: syntax error at or near "ON" at character 85'.

Onko niin, ettei tuota voi toteuttaa ilman JOIN:ia, jotenkin tuolla tavalla mitä minä koetin edellisen viestini lopussa?

Hmm, tulipas laitettua hassusti sulut.

Eli nyt on sulut oikein. (muokkasin edellistä viestiä)

AkeMake kirjoitti:

Onko niin, ettei tuota voi toteuttaa ilman JOIN:ia, jotenkin tuolla tavalla mitä minä koetin edellisen viestini lopussa?

Kyselyssä jossa listaat taulut ja muodostat niiden väliset yhteydet WHERE määrittelyssä, et voi tehdä kuin JOIN tyyppisiä liitoksia. Se, että löydät arvot jotka esiintyvät taulussa X mutta eivät taulussa Y vaatii "X left join Y on ... where Y... is null" tyylisen rakenteen tai sitten alikyselyn.

Ongelmaksi lähestymistavassasi (t1.tuotetun <> t2.tuotetun) tulee nähdäkseni lähinnä se, että kun tuotteita on useampia kuin 2, niin jokaista vuonna 2011 tilattua tuotetta vastaa kaikki muut tuotteet. Eli jos tilausrivillä on tuote 5, niin sitten ko. tilausrivi listaisi kyselyyn mukaan tuotteet 1,2,3,4,6,7,8 jne.

Nuo sulut näyttivätkin melko omituisesti sijoitetuilta ja nyt se näyttää huomattavasti ymmärrettävämmältä. Vaikken kyllä vieläkään ole ihan täysin ymmärtänyt mikä tuossa muuttui niin, että se nyt antoi oikean vastauksen. Pitää jossain vaiheessa syventyä tuohon oikein kunnolla ajatuksen kanssa.

Grez kirjoitti:

Kyselyssä jossa listaat taulut ja muodostat niiden väliset yhteydet WHERE määrittelyssä, et voi tehdä kuin JOIN tyyppisiä liitoksia. Se, että löydät arvot jotka esiintyvät taulussa X mutta eivät taulussa Y vaatii "X left join Y on ... where Y... is null" tyylisen rakenteen tai sitten alikyselyn.

Eli kuten juuri se EXISTS ja alikysely, jota tässä alunperinkin haettiin? Itselleni se ainakin olisi paljon loogisempi kuin tehdä left join ja sen jälkeen ehto, joka tiputtaa osan riveistä pois. Se kertoo semanttisesti mitä haetaan, kun taas join + where antaa ymmärtää, että haetaan tietoja useammasta taulusta tulosjoukkoonkin.

Mutta ehkä se on vain minä?

No siis tottakai sen voi tehdä EXISTS ja alikyselylläkin. Selitin vain miksi se ei toimi ilman alikyselyitä ja ilman LEFT (tai RIGHT) JOINeja.