Select all from X where ID isn't found in table Y -- Is this query possible?

[soma56]

Hi, I'm new to mysql and wanted to know if something like this was possible:

 

 

$query = "SELECT * FROM tableX WHERE status != 'Error' AND id != (SELECT id FROM tableY) LIMIT 1";

              $get_incomplete_keyword = mysql_query($query);

 

The idea is to only select from table 'x' where the id is NOT in table 'Y'. Is this possible?

[awjudd]

-- Slow
SELECT * 
FROM tableX 
WHERE status != 'Error' 
    AND id NOT IN (SELECT id FROM tableY)
LIMIT 1

-- Faster (should work ... ish)
SELECT *
FROM tableX AS x
LEFT JOIN tableY AS y ON x.id=y.id
WHERE x.status != 'Error' AND y.id IS NULL

 

Please Note: the NOT EQUALS and NOT IN are pretty slow options (can't use indexes).

 

~awjudd

[soma56]

Thanks, works perfect.