Databases
StackOverflow2013
Postsdbo
POHow to do equality comparison in SQL with C#-like behavior?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to do equality comparison in SQL with C#-like behavior?
    primarykey
    data
    text
    <p>How to compare equality of value in SQL with null?</p> <p>For those familiar with C#, here are the results of comparing nullable values:</p> <pre><code>null == null : true null == john : false null == paul : false john == null : false john == john : true john == paul : false paul == null : false paul == john : false paul == paul : true </code></pre> <p>The easiest solution I found in SQL is to coalesced the nullable fields into some sentinel value(e.g. 'scoobydoo') then compare them</p> <pre><code>coalesce(A, 'scoobydoo') = coalesce(B, 'scoobydoo') </code></pre> <p>But that is plain kludge if somebody uses the sentinel value, if A happens to be NULL and B is 'scoobydoo', then the expression above would yield true</p> <p>This is exactly my purpose on asking for the logic of the code above (T-SQL UPDATE trigger):</p> <pre><code>-- detect if the value changes if (select invoice_date from inserted) &lt;&gt; (select invoice_date from deleted) begin -- do something to summary tables here end </code></pre> <p>How to do equality comparison in SQL with C#-like behavior?</p> <p>[EDIT: Found the answer <a href="https://stackoverflow.com/questions/680824/sql-equality-inequality-comparison-with-nullable-values">here</a>]</p> <p>Tested the code (Postgres nice boolean support, FTW!):</p> <pre><code>select A, B, A = B, A IS NOT DISTINCT FROM B, -- "logically" same as above A &lt;&gt; B, A IS DISTINCT FROM B -- "logically" same as above from( values (null, null), (null, 'john'), (null, 'paul'), ('john', null), ('john', 'john'), ('john', 'paul'), ('paul', null), ('paul', 'john'), ('paul', 'paul')) as x(A,B) </code></pre> <p>[EDIT: Tested Jon's code, his answer on equality sorts of semi-work(just treat the null as false anyway), but his answer on inequality bombs out]</p> <p>Tested the code (Postgres nice boolean support, FTW!):</p> <pre><code>select A, B, A = B, A IS NOT DISTINCT FROM B, -- "logically" same as above coalesce( (A = B) or (A is null and B is null), false ), -- tested Jon's code for ==, semi-work, coalesced to make it true/false only A &lt;&gt; B, A IS DISTINCT FROM B, -- "logically" same as above (A &lt;&gt; B) and (A is not null or B is not null) -- tested Jon's code for !=, bombs out from( values (null, null), (null, 'john'), (null, 'paul'), ('john', null), ('john', 'john'), ('john', 'paul'), ('paul', null), ('paul', 'john'), ('paul', 'paul')) as x(A,B) </code></pre> <p>[EDIT: posted another <a href="https://stackoverflow.com/questions/1243638/what-are-the-semantics-of-is-distinct-from-and-is-not-distinct-from">question</a> related to this one]</p> <p>[EDIT: posted results based on Jon's inquiry on non-working semantics for inequality comparison]</p> <pre><code>select A, B, A = B, A IS NOT DISTINCT FROM B, -- "logically" same as above (A = B) or (A is null and B is null), -- tested Jon's code for == A &lt;&gt; B, A IS DISTINCT FROM B -- "logically" same as above, (A &lt;&gt; B) and (A is not null or B is not null) -- tested Jon's code for !=, bombs out from( values (null, null), (null, 'john'), (null, 'paul'), ('john', null), ('john', 'john'), ('john', 'paul'), ('paul', null), ('paul', 'john'), ('paul', 'paul')) as x(A,B) a | b | ?column? | ?column? | ?column? | ?column? | ?column? | ?column? ------+------+----------+----------+----------+----------+----------+---------- null | null | null | t | t | null | f | f null | john | null | f | null | null | t | null null | paul | null | f | null | null | t | null john | null | null | f | null | null | t | null john | john | t | t | t | f | f | f john | paul | f | f | f | t | t | t paul | null | null | f | null | null | t | null paul | john | f | f | f | t | t | t paul | paul | t | t | t | f | f | f (9 rows) </code></pre> <p>the non-working semantics for inequality prompted me to post another <a href="https://stackoverflow.com/questions/1243638/what-are-the-semantics-of-is-distinct-from-and-is-not-distinct-from">question</a> :-)</p> <p>[EDIT: Tested Jon's new answer]</p> <pre><code>select A, B, A = B as e, A IS NOT DISTINCT FROM B AS e_works, -- "logically" same as above (A = B) or (A is null and B is null) AS e_semi_work, -- tested Jon's code for ==, works if we treat null as false A &lt;&gt; B as ie, A IS DISTINCT FROM B as ie_works, -- "logically" same as above, (A &lt;&gt; B) and (A is not null or B is not null) as ie_not_work, -- tested Jon's code for !=, bombs out (A &lt;&gt; B) or ((A is null or B is null) and (A is not null or B is not null)) as ie_semi_works, -- this works(well it is, if you treat null as false), not ((A = B) or (A is null and B is null)) as ie_not_work2 -- this doesn't work from( values (null, null), (null, 'john'), (null, 'paul'), ('john', null), ('john', 'john'), ('john', 'paul'), ('paul', null), ('paul', 'john'), ('paul', 'paul')) as x(A,B) </code></pre> <p>Results:</p> <pre><code> a | b | e | e_works | e_semi_work | ie | ie_works | ie_not_work | ie_semi_works | ie_not_work2 ------+------+------+---------+-------------+------+----------+-------------+---------------+-------------- null | null | null | t | t | null | f | f | null | f null | john | null | f | null | null | t | null | t | null null | paul | null | f | null | null | t | null | t | null john | null | null | f | null | null | t | null | t | null john | john | t | t | t | f | f | f | f | f john | paul | f | f | f | t | t | t | t | t paul | null | null | f | null | null | t | null | t | null paul | john | f | f | f | t | t | t | t | t paul | paul | t | t | t | f | f | f | f | f (9 rows) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USHao
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to do equality comparison in SQL with C#-like behavior?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to do equality comparison in SQL with C#-like behavior?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to do equality comparison in SQL with C#-like behavior?
      1. USKieron
      1. VTFavorite
    1. COs/delete/deleted/ in last sample. Give initial values for paul & john explicitly ?
      singulars
      1. POHow to do equality comparison in SQL with C#-like behavior?
      1. USRuben Bartelink
    2. CODup of http://stackoverflow.com/questions/680824/sql-equality-inequality-comparison-with-nullable-values
      singulars
      1. POHow to do equality comparison in SQL with C#-like behavior?
      1. USRuben Bartelink
    3. COIf this is T-SQL, is it definitely a duplicate of a Postgres question? Will the same answer work?
      singulars
      1. POHow to do equality comparison in SQL with C#-like behavior?
      1. USJon Skeet
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload