[SQL] Problemen met NOT EXISTS

[Teeno]

Voor een admin panel in Java wil ik de gebruiker de mogelijkheid geven een user te koppelen (en ontkoppelen) aan een account, hiervoor heb ik twee tabellen naast elkaar met twee buttons (<--- en ---> ertussen).
De tabellen bevatten respectievelijk de accounts die wel en niet gekoppeld zijn aan de gebruiker, de buttons voeren een simpele query uit op de koppeltabel.

De data die in de tabel staat met gekoppelde accounts werkt goed. Echter in de tabel waar de niet gekoppelde accounts in staan gaat het één en ander mis.

Als alle accounts niet aan de user gekoppeld zijn, geeft deze tabel alle accounts weer, zodra ik er ééntje overzet met de knop is direct de hele tabel leeg. Ik gebruik onderstaande query;

SQL:

SELECT * FROM accounts 
WHERE NOT EXISTS(
   SELECT * FROM accounts INNER JOIN con_users_accounts 
   ON con_users_accounts.accountid = accounts.id 
   WHERE con_users_accounts.userid = ?) 
AND NOT parent = ? ORDER BY id

Waarschijnlijk ben ik in de verkeerde hoek aan het zoeken, en merk ik nu de nadelen van beperkte SQL-kennis. Hopelijk is hier iemand met de gouden tip.

[Remus]

Er is geen enkele koppeling tussen het account van je hoofd query en die van de NOT EXISTS query. Dus zodra een gebruiker aan één account gekoppeld is de NOT EXISTS niet waar voor alle accounts.

Zo te zien moet die hele join met accounts uit je NOT EXISTS query, en dan gaat het denk ik wel goed (zolang je de voor waarde het account_id naar de WHERE verplaatst)

[ Voor 4% gewijzigd door Remus op 30-08-2011 21:55 ]

[C0rnelis]

Als je een accounttabel hebt met alle accounts en een tabel waarin een subset van deze accounts in staan. Is het dan niet veel simpeler om de volgende query te gebruiken:

Als ik het goed snap wil je dus iets zoals:

SQL:

SELECT *
FROM accounts
WHERE accounts.id NOT IN (
    SELECT accountid
    FROM con_users_accounts
)

of niet ?

Dit geeft dus simpelweg een lijst terug van alle accounts die niet in con_users_accounts bestaan. Je kan zelf de WHERE toevoegen.

[ Voor 15% gewijzigd door C0rnelis op 30-08-2011 22:02 ]

[Teeno]

C0rnelis schreef op dinsdag 30 augustus 2011 @ 22:00:
Als je een accounttabel hebt met alle accounts en een tabel waarin een subset van deze accounts in staan. Is het dan niet veel simpeler om de volgende query te gebruiken:

Als ik het goed snap wil je dus iets zoals:

SQL:

SELECT *
FROM accounts
WHERE accounts.id NOT IN (
    SELECT accountid
    FROM con_users_accounts
)

of niet ?

Dit geeft dus simpelweg een lijst terug van alle accounts die niet in con_users_accounts bestaan. Je kan zelf de WHERE toevoegen.

Dit lijkt hem te zijn, frappant dat ik zelf ook met NOT IN bezig ben geweest, maar dus vastliep op het feit dat je in je NOT IN query geen SELECT * mag gebruiken, daarom ben ik naar NOT EXISTS gaan kijken.

Nu werkt het inderdaad, beiden bedankt voor de info

[C0rnelis]

Teeno schreef op dinsdag 30 augustus 2011 @ 22:05:
[...]

Dit lijkt hem te zijn, frappant dat ik zelf ook met NOT IN bezig ben geweest, maar dus vastliep op het feit dat je in je NOT IN query geen SELECT * mag gebruiken, daarom ben ik naar NOT EXISTS gaan kijken.

Nu werkt het inderdaad, beiden bedankt voor de info

Dat klopt en is heel logisch. Bij een NOT IN wordt er gekeken naar een enkele kolom. Je doet dus "id NOT IN (SELECT accountid". Je kunt niet de waardes van een kolom filteren met *.

[cariolive23]

Het verschilt per database, maar IN() of NOT IN() geeft bij grotere tabellen vaak performance problemen. NOT EXISTS kan prima werken:

SQL:

SELECT * FROM accounts 
WHERE 
  NOT EXISTS (SELECT null FROM con_users_accounts WHERE accounts.id  = accountid);

Een LEFT JOIN kan ook, maar hou ook hier de performance in de gaten:

SQL:

SELECT * 
FROM 
  accounts 
    LEFT JOIN con_users_accounts ON accounts.id  = accountid
WHERE 
  accountid IS NULL; -- Geen relatie tussen beide tabellen gevonden

[Teeno]

cariolive23 schreef op dinsdag 30 augustus 2011 @ 22:24:
Het verschilt per database, maar IN() of NOT IN() geeft bij grotere tabellen vaak performance problemen. NOT EXISTS kan prima werken:

SQL:

SELECT * FROM accounts 
WHERE 
  NOT EXISTS (SELECT null FROM con_users_accounts WHERE accounts.id  = accountid);

Een LEFT JOIN kan ook, maar hou ook hier de performance in de gaten:

SQL:

SELECT * 
FROM 
  accounts 
    LEFT JOIN con_users_accounts ON accounts.id  = accountid
WHERE 
  accountid IS NULL; -- Geen relatie tussen beide tabellen gevonden

Wat moet ik denken bij "grotere" tabellen, het gaat hier om max 20 users en max 200 accounts, de tabel zal dus niet meer dan 4000 records bevatten.

[Voutloos]

4000 rows is een praktisch lege tabel.

[cariolive23]

Waarschijnlijk zul je weinig verschil merken, maar ga eens met EXPLAIN (of iets soortgelijks van jouw merk database) aan de slag om de verschillende queryplannen te zien.

meten == weten.

[Hydra]

Teeno schreef op dinsdag 30 augustus 2011 @ 22:05:
Dit lijkt hem te zijn, frappant dat ik zelf ook met NOT IN bezig ben geweest, maar dus vastliep op het feit dat je in je NOT IN query geen SELECT * mag gebruiken, daarom ben ik naar NOT EXISTS gaan kijken.

Nogal logisch natuurlijk. Vertaalt is het "geef me accounts waar de id niet in de collectie van (subquery) zit. Als je alle kolommen selecteert weet je RDBMs niet naar welke kolom hij moet kijken.

Edit: spuit 35...

cariolive23 schreef op dinsdag 30 augustus 2011 @ 22:24:
Het verschilt per database, maar IN() of NOT IN() geeft bij grotere tabellen vaak performance problemen.

Mwa. Ik heb zelf onlangs wat rapporten moeten maken op MSSQL met queries met daarin 3 IN()'s die elk zo'n 200k ids opleverden. Zolang je het voor rapportage of admin pages gebruikt is het niet zo'n punt, zolang het maar niet voor elke pageview nodig is, dan is waarschijnlijk je architectuur suboptimaal.

[ Voor 35% gewijzigd door Hydra op 31-08-2011 10:48 ]