SQL: left join ... where -ongelmia

[Teemu Likonen]

Suunnittelen SQLite-tietokantaa ja mietin, kuinka saisin erään kyselyn
toimimaan. Alla on yksinkertaistettu ajatus taulukoista:

CREATE TABLE oppilaat (oppilas_id integer primary key, nimi text, ryhma text);
INSERT INTO "oppilaat" VALUES(1,'Matti','7a');
INSERT INTO "oppilaat" VALUES(2,'Maija','7a');
INSERT INTO "oppilaat" VALUES(3,'Teppo','7a');
INSERT INTO "oppilaat" VALUES(4,'Seppo','7b');

CREATE TABLE arvosanat (oppilas_id integer, suoritus_id integer, arvosana text);
INSERT INTO "arvosanat" VALUES(1,1,'7+');
INSERT INTO "arvosanat" VALUES(2,1,'7');
INSERT INTO "arvosanat" VALUES(1,2,'7½');
INSERT INTO "arvosanat" VALUES(2,2,'8-');
INSERT INTO "arvosanat" VALUES(3,2,'8');

Yritän luoda kyselyn, jossa haetaan oppilaat ja heidän arvosanansa
tietyssä suorituksensa.

select * from oppilaat
left join arvosanat
on oppilaat.oppilas_id = arvosanat.oppilas_id
where ryhma='7a' and suoritus_id=1;

Tämä toimii mutta ei toivotulla tavalla. Se listaa kaikki ryhmän 7a
oppilaat, joilla on arvosanatietue kyseisessä suorituksessa
(suoritus_id=1).

1|Matti|7a|1|1|7+
2|Maija|7a|2|1|7

Pitäisi kuitenkin listata _kaikki_ ryhmän 7a oppilaat eli myös ne,
joilla ei ole arvosanatietuetta suoritus_id=1. suoritus_id ei kuitenkaan
saa olla mikään muu kuin pyydetty 1. Tässä esimerkissä toivottu tulos
olisi tämä:

1|Matti|7a|1|1|7+
2|Maija|7a|2|1|7
3|Teppo|7a|||

Onnistuuko?

[Eero Häkkinen]

Teemu Likonen kirjoitti:

> Yritän luoda kyselyn, jossa haetaan oppilaat ja heidän arvosanansa
> tietyssä suorituksensa.
>
> select * from oppilaat
> left join arvosanat
> on oppilaat.oppilas_id = arvosanat.oppilas_id
> where ryhma='7a' and suoritus_id=1;
>
> Tämä toimii mutta ei toivotulla tavalla. Se listaa kaikki ryhmän 7a
> oppilaat, joilla on arvosanatietue kyseisessä suorituksessa
> (suoritus_id=1).

[...]

> Pitäisi kuitenkin listata _kaikki_ ryhmän 7a oppilaat eli myös ne,
> joilla ei ole arvosanatietuetta suoritus_id=1. suoritus_id ei kuitenkaan
> saa olla mikään muu kuin pyydetty 1.

[...]
> Onnistuuko?

LEFT JOIN ja WHERE yhdessä toimivat lähes kuin INNER JOIN,
eli NULL-arvoja ei toivotulla tavalla mukaan.
LEFT JOIN:in kanssa pitääkin yleensä laittaa kaikki liitosehdot
liitosehtoon.

Tässä tapauksessa siis:

SELECT *
FROM oppilaat
LEFT JOIN arvosanat
ON
arvosanat.oppilas_id = oppilaat.oppilas_id AND
arvosanat.suoritus_id = 1
WHERE ryhma = '7a';

Tällöin LEFT JOIN tuottaa kahdenlaisia rivejä:

(oppilas_id,nimi,ryhma,oppilas_id,suoritus_id,arvosana)-rivejä
(missä suoritus_id on 1) oppilaille, joilla on suoritus 1:

> 1|Matti|7a|1|1|7+
> 2|Maija|7a|2|1|7

(oppilas_id,nimi,ryhma,NULL,NULL,NULL)-rivejä oppilaille,
joilla ei ole suoritusta 1 (koska liitosehdossa rajataan
suoritus_id arvoon 1, ei voida saada riveja, joissa
suoritus_id olisi jotakin muuta):

> 3|Teppo|7a|||

WHERE lauseella rajataan rivin päätaulun eli oppilaiden suhteen
(otetaan mukaan vain ryhmän 7a suoritukset),
mutta ei rajata suorituksia,
joten saadaan rivit kaikille ryhmän oppilaille.

[Teemu Likonen]

Eero Häkkinen kirjoitti 20.9.2013 kello 18.36:

> LEFT JOIN ja WHERE yhdessä toimivat lähes kuin INNER JOIN, eli
> NULL-arvoja ei toivotulla tavalla mukaan. LEFT JOIN:in kanssa pitääkin
> yleensä laittaa kaikki liitosehdot liitosehtoon.
>
> Tässä tapauksessa siis:
>
> SELECT *
> FROM oppilaat
> LEFT JOIN arvosanat
> ON
> arvosanat.oppilas_id = oppilaat.oppilas_id AND
> arvosanat.suoritus_id = 1
> WHERE ryhma = '7a';

Niinpä tietysti. Suurkiitokset!