StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Agree with what everyone else has already said. Simply commenting from another angle, if you try setting <code>ansi_nulls</code> to off, you may get what you expected:</p> <pre><code>set ansi_nulls off if 1 = null select 'a' else select 'b' -- Returned if 1 != null select 'a' -- Returned else select 'b' </code></pre> <p>More info from Books Online:</p> <blockquote> <p>When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.</p> </blockquote> <p>That's <code>ansi_nulls off</code> explained. However, don't be tempted to simply switch it off because:</p> <blockquote> <p>In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.</p> </blockquote> <p>Follow the below recommendation instead:</p> <blockquote> <p>For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values.</p> </blockquote> <pre><code>if 1 is null select 'a' else select 'b' -- Returned if 1 is not null select 'a' -- Returned else select 'b' </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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