在SQL中何时使用“ IS”以及何时使用“ =”

除了标准SQLIS [NOT] NULL中的多个谓词外IS，请参阅SQL 2016 BNF语法

6.39 <boolean value expression> Function

Specify a boolean value.
...
<boolean test> ::=
  <boolean primary>  [ IS [ NOT ] <truth value>  ]

<truth value> ::=
    TRUE
  | FALSE
  | UNKNOWN



8.12 <normalized predicate> Function

Determine whether a character string value is normalized. Format

<normalized predicate> ::=   <row value predicand>  <normalized predicate part 2> 

<normalized predicate part 2> ::=   IS [ NOT ] [ <normal form>  ] NORMALIZED



8.15 <distinct predicate> Function

Specify a test of whether two row values are distinct Format

<distinct predicate> ::=   <row value predicand 3>  <distinct predicate part 2> 

<distinct predicate part 2> ::=   IS [ NOT ] DISTINCT FROM <row value predicand 4>



8.18 <set predicate>
Function

Specify a test of whether a multiset is a set (that is, does not contain any duplicates).
Format

<set predicate> ::=
  <row value predicand>  <set predicate part 2> 

<set predicate part 2> ::=
  IS [ NOT ] A SET




8.19 <type predicate>
Function

Specify a type test.
Format

<type predicate> ::=
  <row value predicand>  <type predicate part 2> 

<type predicate part 2> ::=
  IS [ NOT ] OF <left paren>  <type list>  <right paren> 



8.22 <JSON predicate>
Function

Test whether a string value is a JSON text.
Format

<JSON predicate> ::=
  <string value expression>  [ <JSON input clause>  ]
      IS [ NOT ] JSON
      [ <JSON predicate type constraint>  ]
      [ <JSON key uniqueness constraint>  ]

但是DBMS支持通常很少，例如PostgreSQL支持boolean / type / distinct谓词（而且可能比其他谓词更多）