sql - 检查在sql中不为空？

Question

PostgreSQL 8.4

我有以下一段 sql 脚本:

WHERE p.partner_id IS NOT NULL
    AND (let.external_transaction_registration_date IS NULL OR
 let.external_transaction_registration_date > :date)

检查 not-null 或如果为 null 则不应应用约束的正确方法是否正确？即使 E1 为真，postgresql 是否会计算 (E1 OR E2) 中的表达式 E2？

Answer 1

Does postgresql evalute an exression E2 in (E1 OR E2) (even if E1 is true)?

也许吧。根据 spec 不可预测:

The order of evaluation of subexpressions is not defined. In particular, the inputs of an operator or function are not necessarily evaluated left-to-right or in any other fixed order.

Furthermore, if the result of an expression can be determined by evaluating only some parts of it, then other subexpressions might not be evaluated at all.

但是你的表达是安全的，because :

SQL uses a three-valued logic system with true, false, and null, which represents "unknown".

在 NULL 值的情况下，评估为 (TRUE OR NULL)，即 TRUE。

事实上，这两个表达式都可以满足您的需求(非空或如果为空则不应应用任何约束):

WHERE date_col IS NULL OR date_col > :date
-- vs:
WHERE date_col > :date OR date_col IS NULL

Boolean expressions (AND/OR/NOT combinations) in those clauses can be reorganized in any manner allowed by the laws of Boolean algebra.

这也是applies至 PostgreSQL 8.4 .