Mysql – Change-set SQL optimization

MySQL

I'm trying to clean up a change-set query that returns rows from a table that have changed since a given time and also do not exist in a known list of already previously returned results.

select ID, Field1, Field2
from Table1
where Field = 'Foo'
and (
    ID not in ( %s )
    or (
       ID in ( %s ) and 
       GREATEST(created, COALESCE(modified, from_unixtime(0))) > from_unixtime(:stamp)
    )
)

Now using some basic 9th grade logic operators….

A or (B and C) = (A or B) and (A or C)

Except in my instance, A = "id not in (%s)" and B = !A. C = the date constraint. Which reduces to…

(A or !A) and (A or C) => A or C

So, following that logic, my where clause should now look like this, but it seems funky. Can anybody more experienced with SQL and/or logic tell me if I've missed something simple?

select ID, Field1, Field2
from Table1
where Field1 = 'Foo'
and (
    id not in ( %s )
    or GREATEST(created, coalesce(modified, from_unixtime(0))) > from_unixtime(:stamp)
)

Does that look right?

Best Answer

Yes, it looks all right. (but only looks so)

Beware! SQL logic is a 3-valued logic, not the same as the "usual" math logic. The three values are TRUE, FALSE and UNKNOWN.

In your case, if the id or the values in the ( %s ) list contain NULL, the two versions are not equivalent. Example: 5 NOT IN (6, 7, NULL) and 5 IN (6, 7, NULL) will both evaluate to UNKNOWN so no row will be returned by the 1st version. While the 2nd version will return the rows that satisfy the other condition greatest() ....

If you have no Nulls, the two versions are equivalent.

You can find the truth tables in Wikipedia: Three-valued logic

Related Question