Three valued logic

Because of the way SQL handles NULL, there are some special operationsavailable for expressing:

• IS NULL
• IS NOT NULL
• IN
• NOT IN

While it would be possible to use the IS NULL and IN operators with thenegation operator (~), sometimes to get the correct semantics you will needto explicitly use IS NOT NULL and NOT IN.

The simplest way to use IS NULL and IN is to use the operatoroverloads:

# Get all User objects whose last login is NULL.
User.select().where(User.last_login >> None)
 
# Get users whose username is in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username << usernames)

If you don’t like operator overloads, you can call the Field methods instead:

# Get all User objects whose last login is NULL.
User.select().where(User.last_login.is_null(True))
 
# Get users whose username is in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.in_(usernames))

To negate the above queries, you can use unary negation, but for the correctsemantics you may need to use the special IS NOT and NOT IN operators:

# Get all User objects whose last login is *NOT* NULL.
User.select().where(User.last_login.is_null(False))
 
# Using unary negation instead.
User.select().where(~(User.last_login >> None))
 
# Get users whose username is *NOT* in the given list.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(User.username.not_in(usernames))
 
# Using unary negation instead.
usernames = ['charlie', 'huey', 'mickey']
User.select().where(~(User.username << usernames))