The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

While debugging some SA V11 SQL Queries I found 'where colname = Null' sometimes used interchangeably with 'where colname Is Null' as a test for Null values in a column. I am not sure that they produce the correct result yet the SA will accept the syntax.

Sometimes when creating unions I also see 'colname = Null' used to create a dummy null column used to make sure the union queries are aligned. And of course there is the IsNull() function which can be used to assign values to Nulls in result sets.

What is the correct syntax , are 'where colname = Null' and 'where colname IS Null' equivalent

If not what is the difference in the behavior.

asked 10 Jan '12, 16:19

Glenn%20Barber's gravatar image

Glenn Barber
1.1k234051
accept rate: 0%

edited 10 Jan '12, 16:32


Regarding NULLs, you need to consider three-valued logic. The following page in the documentation helps explain this:

NULL value

What is important to note is that equality with NULL can not be evaluated. For this reason NULL = NULL is not TRUE or FALSE but instead "evaluates" to UNKNOWN. Rows with which a WHERE clause evaluates to UNKNOWN are excluded from the result set. If you wish to compare a value with NULL you must use IS NULL or IS NOT NULL. *You may also use the less common IS UNKNOWN predicates for some tasks.

permanent link

answered 10 Jan '12, 17:44

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

edited 10 Jan '12, 17:45

2

To compare nullable columns or expressions, as of SQL Anywhere 12 one can use the IS NOT DISTINCT FROM search condition, which has the advantage of being sargable.

(11 Jan '12, 13:08) Graham Hurst

(Preface: Surely a true SQL authority like Glenn Paulley will give the full answer...)

I would highly recommend to use the "col IS NULL" comparison predicate for WHERE and HAVING clauses as that truly fits SQL's three valued logic. An alternative would be to use "col = NULL IS UNKNOWN".

The "col = NULL" syntax may also return TRUE, particularly for Transact-SQL compatibility, e.g. when the ansinull option is set to off. However, then the evaluation may be dependent on the setting of such an option (which will be set automatically by certain client types..). That doesn't look too reliable in my book...


The "col = NULL" syntax in a UNION seems to be a totally different topic. IMHO, that might be the T-SQL syntax to use an alias in the select list, such as

select col1, col2, col3 = col1 + col2, col4 = null from dummy

which would be equivalent to the following Watcom-SQL syntax:

select col1, col2, col1 + col2 as col3, null as col4 from dummy

and therefore is just a way to specify NULL as a select list item and give that expression a name.

permanent link

answered 10 Jan '12, 17:45

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

edited 10 Jan '12, 17:48

3

Using col = NULL (and expecting TRUE) is Transact-SQL syntax. That predicate will return TRUE if and only if ANSINULL is set to OFF, and therefore I never recommend its use because changing SQL dialects now changes results. In ANSI-standard SQL, anything compared to NULL evaluates to UNKNOWN and in a false-interpreted situation such as a WHERE clause the predicate will be interpreted as FALSE.

Sybase ASE also supports the IS [NOT] NULL predicate so there is no reason to stick to the = NULL usage.

Volker's remarks about the use of = in a SELECT list, which is also a T-SQL construction, requires some clarification. SELECT in T-SQL is overloaded; it is (also) Sybase ASE's implementation of the SET statement. So one would specifiy

SELECT X = NULL

to set the variable X to NULL, rather than the Watcom syntax

SET X = NULL;

Yes, in Transact-SQL the = is ALSO overloaded for alias specification, ie

SELECT X = Y, B = G

adds aliases to each select list expression, equivalent to

SELECT X AS Y, B AS G

in the Watcom dialect. Assignment in T-SQL is only permitted when the SELECT statement has no FROM clause and there is a single select-list expression.

(11 Jan '12, 07:45) Glenn Paulley

FWIW, the following FAQ might be helpful, too.


BTW: I have noticed that with the help of the "Related questions" bar on the right - a very useful feature as long as questions are somewhat reasonable tagged:) - Breck, so waddayathank?

permanent link

answered 10 Jan '12, 17:52

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18

question asked: 10 Jan '12, 16:19

question was seen: 5,303 times

last updated: 11 Jan '12, 13:08