Re: [sqlite] WHERE clause syntax error

[Gerry Snyder]

James W. Walker wrote:
Let's say I have two tables A and B, each of which has an integer ID 
column.  I want to find ID values that occur in A but not B.  The 
first query I came up with was:


SELECT ID FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE B.ID = A.ID);

but I get a syntax error.  My next try,

SELECT ID FROM A WHERE ID NOT IN (SELECT ID FROM B);

works and is probably more efficient, but I'm just curious what's 
wrong with the first one.


Not sure whether the following is more efficient, but it is easier on 
the eyes:


SELECT ID FROM A EXCEPT SELECT ID FROM B;


HTH,

Gerry

Re: Re: [sqlite] WHERE clause syntax error

[Miha Vrhovnik]

>Something like:
>
>SELECT A.ID
>FROM A
>LEFT JOIN B
>ON A.ID = B.ID
>WHERE B.ID IS NULL
>
>Might be more efficient?
Nope. I rewrote such query to SELECT ID FROM A WHERE ID NOT IN (SELECT ID FROM 
B);

SELECT COUNT(*) AS unread FROM mboxes WHERE
 idMbox = %d AND idMsg IN (SELECT idMsg FROM
 msgStatus WHERE idStatus = %d);

Is almost instant
Where as
SELECT COUNT(*) AS unread FROM msgStatus AS b
  JOIN mboxes AS a ON b.idMsg = a.idMsg WHERE a.idMbox = %d
  AND b.idStatus = %d;

Would take about 9 seconds.
mboxes has 18.000 rows, msgStatus 23.000
So if one would like to argue, I can provide additional details.

Regards,
Miha

Re: [sqlite] WHERE clause syntax error

[Mikey C]

Something like:

SELECT A.ID
FROM A
LEFT JOIN B
ON A.ID = B.ID
WHERE B.ID IS NULL

Might be more efficient?
--
View this message in context: 
http://www.nabble.com/WHERE-clause-syntax-error-t1801701.html#a4912687
Sent from the SQLite forum at Nabble.com.

[sqlite] WHERE clause syntax error

[James W. Walker]

Let's say I have two tables A and B, each of which has an integer ID 
column.  I want to find ID values that occur in A but not B.  The 
first query I came up with was:


SELECT ID FROM A WHERE NOT EXISTS (SELECT * FROM B WHERE B.ID = A.ID);

but I get a syntax error.  My next try,

SELECT ID FROM A WHERE ID NOT IN (SELECT ID FROM B);

works and is probably more efficient, but I'm just curious what's 
wrong with the first one.

--
  James W. Walker, ScriptPerfection Enterprises, Inc.