title: Why use IS DISTINCT FROM - Postgres
body: <p>What is the best use of this function in <code>Postgres</code> <code>IS DISTINCT FROM</code>, auditioning got the same result using <code>COALESCE</code> but in less time , following the test :</p>
  
  <pre><code>SELECT COUNT(P.id)
  
  FROM produto P
    INNER JOIN cliente CL ON P.id_cliente = CL.id_cliente
  
  WHERE 
    COALESCE(CL.tp_pessoa,'') &lt;&gt; 'JURIDICA' -- test with COALESCE, average 610 ms
  
    (CL.tp_pessoa &lt;&gt; 'JURIDICA' OR CL.tp_pessoa IS NULL) -- test with OR, average 668 ms
  
    CL.tp_pessoa IS DISTINCT FROM 'JURIDICA' -- test with IS DISTINCT FROM, average 667 ms
  
    OUTRO TESTE:
  
    COALESCE(CL.tp_pessoa,'') &lt;&gt; COALESCE(P.observacao,'') -- test with IS DISTINCT FROM, average 940 ms
  
    CL.tp_pessoa IS DISTINCT FROM P.observacao -- test with ```IS DISTINCT FROM```, average 930 ms, a little beter here
  </code></pre>
  
  <p>Its have lower performance and is a function that is not found in other DBs such as <code>SQL Server</code> , another reason to not use it .</p>
  
  <p>Doing another test, where both criteria can be <code>NULL</code> , the <code>IS DISTINCT FROM</code> had a slight advantage , this would be its use , where more it applies ?</p>
  
  <p>Edit:</p>
  
  <p>Like @hvd said is that it is part of <code>ANSI SQL</code> and the result of <code>COALESCE(CL.tp_pessoa,'') &lt;&gt; COALESCE(P.observacao,'')</code> is not the same of <code>CL.tp_pessoa IS DISTINCT FROM P.observacao</code>.</p>