[SQL] rij uitsluiten als een id niet in een tabel voorkomt

[Verwijderd]

Ik heb het volgende probleem

Ik heb een tabel users met de kolommen

userID
categoryID


en een tabel categories met de kolommen:

id
name


Nu wil ik alle users selecteren die een categoryID hebben die niet in de tabel categories voorkomt. Hoe doe ik dat?

Ik heb al zoiets geprobeerd, maar dit sluit niet voldoende uit.

code:

SELECT 
  u.userID
FROM
  users u,
  categories c
WHERE
  u.categoryID != c.id
GROUP BY
  u.userID

[ Voor 5% gewijzigd door Verwijderd op 28-01-2005 16:26 ]

[Verwijderd]

Je moet wel je database laten matchen ?

Waar vergelijk je die op ?

Want deze query kan echt niet/

[beetle71]

code:

SELECT u.userid FROM users as u LEFT OUTER JOIN categories as c ON 
u.categoryID=c.id WHERE categoryID is NULL

Volgens mij doet dit wat je wil (in MySQL tenminste)

[CyBeR]

code:

SELECT * FROM users WHERE categoryid NOT IN (select id from categories)

Als je een DB hebt die subqueries ondersteunt (kweenie of mysql dat nu eindelijk al eens kan). Overigens zou je dergelijke dingen kunnen voorkomen door referentiele integriteit goed te regelen

[bigbeng]

beetle71 schreef op vrijdag 28 januari 2005 @ 16:34:

code:

SELECT u.userid FROM users as u LEFT OUTER JOIN categories as c ON 
u.categoryID=c.id WHERE -->c.<--categoryID is NULL 
-->AND NOT u.categoryid IS NULL<--

Volgens mij doet dit wat je wil (in MySQL tenminste)

Volgens mij moet je nog even aangeven in de WHERE statement dat het om c.categoryID gaat die Null moet zijn en dat u.categoryid niet null mag zijn.(zie quote)

[curry684]

CyBeR schreef op vrijdag 28 januari 2005 @ 16:37:

code:

SELECT * FROM users WHERE categoryid NOT IN (select id from categories)

Als je een DB hebt die subqueries ondersteunt (kweenie of mysql dat nu eindelijk al eens kan). Overigens zou je dergelijke dingen kunnen voorkomen door referentiele integriteit goed te regelen

Left join met IS NULL geeft hetzelfde resultaat, is handiger construeren en prolly sneller:

SQL:

SELECT DISTINCT u.user_id
FROM users AS u
LEFT OUTER JOIN category AS c ON u.categoryid = c.id
WHERE c.id IS NULL;

edit:
erm wat beetle71 dus ook zegt, ik had die even niet als hetzelfde herkend door het gebrek aan enters

[ Voor 9% gewijzigd door curry684 op 28-01-2005 16:56 ]

[CyBeR]

curry684 schreef op vrijdag 28 januari 2005 @ 16:55:
[...]

Left join met IS NULL geeft hetzelfde resultaat, is handiger construeren en prolly sneller:

SQL:

SELECT DISTINCT u.user_id
FROM users AS u
LEFT OUTER JOIN category AS c ON u.categoryid = c.id
WHERE c.id IS NULL;

edit:
erm wat beetle71 dus ook zegt, ik had die even niet als hetzelfde herkend door het gebrek aan enters

Handiger construeren? Vind je? 1 regeltje vs. 3 regels

[curry684]

CyBeR schreef op vrijdag 28 januari 2005 @ 16:58:
[...]

Handiger construeren? Vind je? 1 regeltje vs. 3 regels

Ik doelde op het feit dat je de IN-clause handmatig uit moet schrijven omdat dat belachelijk excuus voor een DBMS vast nog geen subqueries ondersteunt

Joinen zal sowieso sneller zijn tho met goeie indexing.

[CyBeR]

curry684 schreef op vrijdag 28 januari 2005 @ 17:01:
[...]

Ik doelde op het feit dat je de IN-clause handmatig uit moet schrijven omdat dat belachelijk excuus voor een DBMS vast nog geen subqueries ondersteunt

Mja ik snap ook niet waarom 't zo populair is PostgreSQL is even vrij en ondersteunt veel meer.

Joinen zal sowieso sneller zijn tho met goeie indexing.

Dat zal ik niet ontkennen.

[bigbeng]

Was er niet iets met het niet trashen van applicaties, operating systems en bedrijfven enzo? Ik heb de richtlijnen al een tijdje niet doorgenomen.

[curry684]

bigbeng schreef op vrijdag 28 januari 2005 @ 17:05:
Was er niet iets met het niet trashen van applicaties, operating systems en bedrijfven enzo? Ik heb de richtlijnen al een tijdje niet doorgenomen.

Alleen als je het niet onderbouwd, en gelukkig is dat met MySQL niet zo'n probleem

Ik heb trouwens even een 'vergelijkbaar geval' getest, en daarin was de versie met joins estimated 20% sneller op SQL Server dan de versie met de subquery. Zodra je een correlated subquery nodig gaat hebben (die MySQL al helemaal niet ondersteunt) wordt dit een veelvoud.

[Verwijderd]

beetle71 schreef op vrijdag 28 januari 2005 @ 16:34:

code:

SELECT u.userid FROM users as u LEFT OUTER JOIN categories as c ON 
u.categoryID=c.id WHERE categoryID is NULL

Volgens mij doet dit wat je wil (in MySQL tenminste)

Bedankt! Dit doet inderdaad wat ik wil. De uiteindelijk code:

code:

SELECT 
                 u.id,
                 u.username
FROM
                 users_users u,
                 friends_userFriends f
LEFT JOIN
                 friends_categories c
ON
                 f.categoryID = c.id 
WHERE
                 f.userID = " . $_SESSION[ 'userID' ] . "
AND
                 c.id IS NULL 
AND
                 f.friendID = u.id
ORDER BY
                 u.username

[Verwijderd]

Laat maar.

[ Voor 99% gewijzigd door Verwijderd op 29-01-2005 11:14 ]

[curry684]

Geen idee, heb de queries niet meer waarmee ik probeerde maar die kwamen op 44.44% resp. 55.55% "of the full batch" uit SQL Server en MySQL mag je overigens wat betreft query optimization absoluut niet vergelijken eigenlijk, zie ook:

quote: http://vulcanus.its.tudelft.nl/acm/got/antimysql.php

11. MySQL has trouble optimising complex queries, SELECT ... FROM ... WHERE something BETWEEN ... can be considerd complex in some cases...

Ik heb SQL Server stunts zien uithalen met verschillende queries op basis van subqueries, cross joins en reguliere joins die allemaal in hetzelfde execution plan resulteerden. Een subquery moet je in principe gewoon altijd zien te voorkomen als je het met een join af kunt, omdat het *at best* even snel is dan een goed geindexeerde join. Zonder indexes kan het overigens een ander verhaal worden alweer D'r zijn heel veel randvoorwaarden die meespelen, zelfs of je een clustered of non-clustered index gebruikt op de relevante fields.

[Verwijderd]

Verwijderd schreef op zaterdag 29 januari 2005 @ 11:08:
Een paar maanden terug hier een lange discussie over gevoerd. Eigenlijk zou het bij een ongecorreleerde subquery niet mogen uitmaken, de optimizer moet uiteindelijk met hetzelfde plan op de proppen komen. Haploid heeft toen ook getest en raar maar waar. Daar maakte het geen verschil. Een 20% snellere join lijkt mij dan ook erg vreemd. Enig idee hoe het komt?

Als je alles in één query gooit, gaat de optimizer voor die hele query een plan maken. Gebruik je subquerys, dan maakt hij eerst voor de subquery een plan, en dan voor de hoofdquery.

Nu kan dit betekenen dat het plan voor de gehele query er anders uit komt te zien dan het plan voor de query+subquery, met behoorlijke performance verschillen.

Interbase 5.6NT is hier helemaal dramatisch in. Daar kan het een factor 1000 uitmaken (zeg ik uit ervaring).

[Verwijderd]

Verwijderd schreef op zaterdag 29 januari 2005 @ 11:14:
[...]


Als je alles in één query gooit, gaat de optimizer voor die hele query een plan maken. Gebruik je subquerys, dan maakt hij eerst voor de subquery een plan, en dan voor de hoofdquery.

Nu kan dit betekenen dat het plan voor de gehele query er anders uit komt te zien dan het plan voor de query+subquery, met behoorlijke performance verschillen.

Interbase 5.6NT is hier helemaal dramatisch in. Daar kan het een factor 1000 uitmaken (zeg ik uit ervaring).

Verschil tussen de query die ik neergooide was dat daar een IN ipv een NOT IN gedaan werd. Zou niet moeten uitmaken maar goed, niet helemaal vergelijkbaar en ik had het al weer weggehaald.

Wat jij zegt is waar, maar is voornamelijk een probleem van een slechte optimizer. Ook maakt het voor ongecorreleerde sub queries niet uit.
Om te joinen zul je een table scan (of index search) moeten doen en daarna de velden waarop je joint moeten selecteren, bij een subquery geldt precies hetzelfde.

[curry684]

Verwijderd schreef op zaterdag 29 januari 2005 @ 11:29:
[...]

Om te joinen zul je een table scan (of index search) moeten doen en daarna de velden waarop je joint moeten selecteren, bij een subquery geldt precies hetzelfde.

Behalve dat een join per definitie op basis van 2 index scans plaatsvind en een subquery kan resulteren in een losse index scan en daarna een index scan met searches in temp result table van de subquery. Voor een correlated subquery is het helemaal een ramp: dan heb je in principe een index scan op de table met per row een extra index scan op de subquery-tabel. Gelukkig optimaliseert SQL Server dat soort dingen fantastisch weg, maar met MySQL zou ik daar niet op rekenen

[Killemov]

In dit simpele geval zou het voor een beetje query-optimizer lood om oud ijzer moeten zijn, daar zou hetzelfde plan uit moeten komen. Trek de LEFT JOIN of NOT IN afweging echter nooit door naar algemeen geldend advies.