when to use "IS" and when to use "=" in SQL

Question

When Can I use the syntax WHERE IS .... and Where ..=..

I see some posts saying Null can only be used with IS, but what else I can use IS with?

Thanks in advance

I modified the question. to see what other stuff we can utilise IS with? Thanks — Misho, Nov 10 '19 at 09:23
IS is only used for IS NULL or IS NOT NULL. Since NULL can be understood as "unknown" one can't say x = NULL. — LukStorms, Nov 10 '19 at 09:27
Thanks Lukstorms, Does that mean we don't use IS in other condition — Misho, Nov 10 '19 at 09:29
As far as I know, at least in standard SQL, the IS keyword is only used to check if a field has a value or not. And in most DBMS an empty string isn't NULL. — LukStorms, Nov 10 '19 at 09:34
@LukStorms In Standard SQL there are lots of predicates based on IS besides NULL, at least: `IS NORMALIZED| TRUE|FALSE|UNKNOWN|DISTINCT FROM|A SET|OF` — dnoeth, Nov 10 '19 at 10:53
@dnoeth. Heh, didn't think those would be part of the standard. O_O Thanks for that comment. :-) — LukStorms, Nov 10 '19 at 11:05

score 2 · Accepted Answer · answered Nov 10 '19 at 11:28

Besides IS [NOT] NULL there are several predicates based on IS in Standard SQL, see SQL 2016 BNF syntax

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>  ]

But DBMS support is usually low, e.g. PostgreSQL supports boolean/type/distinct predicates (and that's probably more than others)

when to use "IS" and when to use "=" in SQL

1 Answers1