[SQL] Query alternatieven

[slashhead89]

Hallo

Ik heb voor mijn studie een SQL opdracht moeten maken. Nou heb ik die ingeleverd en heb de feedback gekregen dat de left join een ongebruikelijke methode is om dit probleem op te lossen. Ik kan zelf echter niet achter de gebruikelijke methode komen.

Dit is de situatie: er zijn 3 tabellen:
"product" met kolommen "maker" en "model"
"PC" met kolommen "model" en "price"
"laptop" met kolommen "model", "framesize" en "price"

En de opdracht was: Geef alle makers weer die laptops maken met een framesize>17 en die geen pc's maken.

Mijn code was:

SQL:

SELECT
maker
FROM product
LEFT JOIN laptop ON laptop.model = product.model
LEFT JOIN pc ON pc.model = product.model
GROUP BY maker
HAVING COUNT(pc.model) = 0 AND MIN(laptop.framesize) >= 17

Heeft iemand een idee hoe dit anders aangepakt kan worden, en waarom is die code gebruikelijker of beter?

[matthijsln]

slashhead89 schreef op woensdag 09 mei 2012 @ 21:22:
En de opdracht was: Geef alle makers weer die laptops maken met een framesize>17 en die geen pc's maken.

Mijn code was:

code:

SELECT
maker
FROM product
LEFT JOIN laptop ON laptop.model = product.model
LEFT JOIN pc ON pc.model = product.model
GROUP BY maker
HAVINGCOUNT(pc.model) = 0 AND MIN(laptop.framesize) >= 17

Heeft iemand een idee hoe dit anders aangepakt kan worden, en waarom is die code gebruikelijker of beter?

Op zich is je query niet slecht, alleen moet je in plaats van min(laptop.framesize) >= 17 natuurlijk max(laptop.framesize) >= 17 gebruiken (het gaat er niet om dat de maker alleen laptops met framesize >= 17 maakt, maar dat de grootste dat is zodat er minimaal 1 is).

Het klopt dat het wel een beetje een ongebruikelijke query is. Ik vind niet zozeer om de left joins, maar omdat het uitrekenen van de having expressies niet nodig is. Voor uitrekenen moet het dbms intern een full join doen.

Een andere manier is om eerst de makers die laptops met framesize > 17 te selecteren maar daar de pc-makers uit te filteren. Waarschijnlijk levert dit betere performance op. Bijvoorbeeld:

SQL:

select maker
from laptop
join product on laptop.model=product.model
where framesize > 17
except
select maker from pc join product on pc.model=product.model

[0xDEADBEEF]

Hm... 'EXCEPT, welk DBMS kan daarmee omgaan?
mysql blijkbaar niet: http://dev.mysql.com/doc/refman/5.0/en/select.html http://dev.mysql.com/doc/refman/5.0/en/expressions.html
maar postgresql wel: http://www.postgresql.org/docs/8.2/static/sql-select.html

@matthijsln bedoel je misschien MINUS in plaats van EXCEPT?

[matthijsln]

0xDEADBEEF schreef op woensdag 09 mei 2012 @ 22:29:
Hm... 'EXCEPT, welk DBMS kan daarmee omgaan?
mysql blijkbaar niet: http://dev.mysql.com/doc/refman/5.0/en/select.html http://dev.mysql.com/doc/refman/5.0/en/expressions.html

Tja, mysql...

maar postgresql wel: http://www.postgresql.org/docs/8.2/static/sql-select.html

@matthijsln bedoel je misschien MINUS in plaats van EXCEPT?

Op PostgreSQL werkt dit "gewoon" prima. Dit kan ook nog als alternatief:

SQL:

select distinct maker
from laptop
join product on laptop.model=product.model
where framesize>17
and maker not in (select maker from pc join product on pc.model=product.model)

[Verwijderd]

0xDEADBEEF schreef op woensdag 09 mei 2012 @ 22:29:
Hm... 'EXCEPT, welk DBMS kan daarmee omgaan?

Niet erg boeiend. EXCEPT is standaard SQL. Dit gaat om theorie, met misschien wat optimalisaties voor in de praktijk.

[slashhead89]

matthijsln schreef op woensdag 09 mei 2012 @ 22:17:
[...]


Het klopt dat het wel een beetje een ongebruikelijke query is. Ik vind niet zozeer om de left joins, maar omdat het uitrekenen van de having expressies niet nodig is. Voor uitrekenen moet het dbms intern een full join doen.

Een andere manier is om eerst de makers die laptops met framesize > 17 te selecteren maar daar de pc-makers uit te filteren. Waarschijnlijk levert dit betere performance op. Bijvoorbeeld:

[...]

Het MIN gedeelte klopt wel met de opdracht. Ik had alleen even iets te vlug getypt en het woordje "alleen" vergeten in de vraagstelling te typen. Ik moest dus alle makers zoeken die alleen laptops maken(dus niet kleiner) met grootte 17 of groter.

De code die jij suggereert geeft inderdaad al een wat betere performance op een kleine testserver.

Tijdens de colleges is de tip gegeven om voor deze opdracht specifiek te kijken naar de having code. Dat heb ik dus ook gewoon gedaan. Ik neem dus aan dat de docenten dat gedeelte niet ongebruikelijk zullen vinden als ze zelf die tip geven.
Dus ik vraag me nog wel een beetje af waarom ze de left join wel ongebruikelijk zouden vinden? Is dat enkel een performance zaak?

[ Voor 12% gewijzigd door slashhead89 op 09-05-2012 23:12 ]

[DEiE]

Met een left join pak je alle makers, met eventueel de laptops die ze maken. Als ze géén laptops maken, krijg je ook de maker terug. Dit ziet er zo uit, waarbij A de producttabel is, en B de laptoptabel.

Deze makers zijn in jouw geval niet van relevantie, omdat je enkel makers wilt hebben die laptops maken. Jouw geval ziet er zo uit.


Jij haalt dus de makers op, ook al hebben ze geen laptops. Door deze met een 'normale' join te joinen, filter je daar alle makers die geen laptops maken er al uit. Ik denk dat je docent daarop doelde bij zijn feedback.

[Martijn19]

De bron van de post hierboven is Coding Horror.
Dit artikel heeft mij joins goed kunnen uitleggen

[DEiE]

En anders is de FAQ ook erg uitgebreid, alleen misschien een beetje teveel informatie voor de beginnende joiner.