0
select A from table1 where A <> '-'

This query excludes - and also the null values. Why the null values?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Jack
  • 510
  • 3
  • 6
  • 22

2 Answers2

1

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

See Reference Here:

Not equal <> != operator on NULL

Just look for the proper keywords for you to browse for results to your problem efficiently

Community
  • 1
  • 1
Dexter Raymund De Guia
  • 95
  • 1
  • 11
0

NULL values are always exluded if you don't include them explcitely via IS NULL. NULL means not unknown. You can't select them with = NULL or omit them with <> NULL. Any comparison with NULL results not in true or false but in unknown.

You have to use

SELECT A from table1 where A IS NULL OR A <> '-';

another way using COALESCE:

SELECT A from table1 where COALESCE(A, '') <> '-';

Read: https://en.wikipedia.org/wiki/SQL#Null_or_three-valued_logic_.283VL.29

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Thanks Tim, but my question is different. i am using condition where A <> '-' (dash) but this condition also exclude my null values from table – Jack Sep 18 '15 at 08:57
  • @DatabasePirate: yes, that's what i was trying to say with my answer. `NULL` values are never included in the resultset unless you don't ask for them with `IS NULL` or `IS NOT NULL`. A comparison with NULL (like you do with `NULL <> '-'`) will always result in `NULL` not in `true`/`false`. `NULL` is neither equal nor unequal to any value, even to another `NULL` value. In some rdbms you can deactivate this behaviour, f.e. in SQL-Server with `SET ANSI_NULLS OFF`. – Tim Schmelter Sep 18 '15 at 09:03