Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
    
Well I really appreciate you looking at it. I'll investigate whether or not nulls come into play. Thanks again! –  Nerd Apr 7 '12 at 17:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.