Postgresql – Why does null and true evaluate to null but null or true evaluates to true

Boolean logic - or Three valued logic

• IN is shorthand for a series of OR conditions
• x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL)
• ... is the same as x <> 1 AND x <> 2 AND x <> NULL
• ... is the same as true AND true AND unknown **
• ... = unknown **
• ... which is almost the same as false in this case as it will not pass the WHERE condition **

Now, this is why folk use EXISTS + NOT EXISTS rather than IN + NOT IN. Also see The use of NOT logic in relation to indexes for more

** Note: unknown is the same as false at the end of an expression in a WHERE condition.
While the expression is being evaluated, then it is unknown
See @kgrittn's comment below for why

PostgreSQL does not return these instead of boolean values. It is some clients (for example, psql and pgAdminIII) which represents TRUE with t and FALSE with f - try the same query in another client and you will see something else. See, for example, what DBVisualizer gives you:

I guess the reason for showing t and f is simply sparing space in a command-line client that lacks horizontal scrolling abilities.

Note: I am in no way affiliated to DbVis Software.