Doing a SQLite MATCH using only negated terms

Question

I recently found that this is impossible in SQLite:

 SELECT * FROM fruit WHERE fruit MATCH '-apple'

Yet this is possible:

 SELECT * FROM fruit WHERE fruit MATCH 'pear -apple'

I tried this using FTS3 and FTS4 with the same results. Why does match require at least one non-negated term? And how do I work around this limitation? I need to return all fruits that don't match "apple"...that's it. Any ideas?

Answer 1

Something like this will work:

SELECT * FROM fruit 
WHERE fruit.oid NOT IN 
(
    SELECT oid FROM fruit WHERE fruit MATCH 'apple'
)

Answer 2

Why don't you simply invert the search?

select * from fruit where fruit NOT match 'apple'

You could use a EXCEPT clause:

select * from fruit except select * from fruit where fruit match 'apple';

but that might not be very efficient.