How come this one works
SELECT DISTINCT a.d
FROM a,b
WHERE a.c
NOT IN (SELECT b.c FROM b)

but this one doesn't
SELECT DISTINCT a.d,a.c
FROM a
LEFT JOIN b USING (c) WHERE a.c<>b.c


I'm trying to get the rows in a.c that don't match b.c but the 2nd one
should be faster.
thanks, Janis



TABLE A TABLE B

col d col c col c
____________ ____________
1 apple apple
2 banana kiwi
3 orange banana
4 grapefruit blackberry
5 cherry persimmon
6 NULL apple
thanks,

On Mar 2, 8:35*am, jrough <janis.ro... (AT) gmail (DOT) com> wrote:
Please disregard, I have it.


SELECT DISTINCT a.d, a.c
FROM a
INNER JOIN b ON a.c <> b.c
WHERE a.c NOT
IN (

SELECT b.c
FROM b
)
LIMIT 0 , 30

In article <e2adaaf6-b260-46e3-829c-46d060abbb43 (AT) o30g2000pra (DOT) googlegroups.com>, jrough <janis.rough (AT) gmail (DOT) com> wrote:
Whatever you have, I hope it's not contagious.

On 2011-03-02 22:16, jrough wrote:
[...]
Not sure what it is you are trying to do, does the following give the
correct answer?

SELECT a.d, a.c
FROM a
LEFT JOIN b
USING (c)
WHERE b.c IS NULL

Another option:

SELECT a.d, a.c
FROM a
WHERE NOT EXISTS (
SELECT 1
FROM b
WHERE a.c = b.c
)

/Lennart