Output of Query

  • Simple question, thanks for the reminder.

    -- Gianluca Sartori

  • As the correct output is Null,'Is Zero', 'Is One', 'Is Two'.

    I want to know why NULL is also the part of the output.

    The explanation says that:

    The statement CASE COL1 WHEN NULL THEN 'Is Null' doesn't evaluate to TRUTH value as NULL represents MISSING value.

    Hence it returns NULL.

    As the statement CASE COL1 WHEN NULL THEN 'Is Null' is not evaluated to TRUTH,then in that case it should not return any values as there is no else condition mentioned in the statement.

    Kindly help me to understand this.

  • jigsm_shah (10/11/2011)


    As the correct output is Null,'Is Zero', 'Is One', 'Is Two'.

    I want to know why NULL is also the part of the output.

    The explanation says that:

    The statement CASE COL1 WHEN NULL THEN 'Is Null' doesn't evaluate to TRUTH value as NULL represents MISSING value.

    Hence it returns NULL.

    As the statement CASE COL1 WHEN NULL THEN 'Is Null' is not evaluated to TRUTH,then in that case it should not return any values as there is no else condition mentioned in the statement.

    Kindly help me to understand this.

    The answer is in the else condition -- if there's no else, then the result is null.

    You can verify this by adding an additional value in the tmp subquery, say 10 or 42), you'll get nulls for those rows.

  • nice question , it depends on ansi_nulls settings

    Statement 1 -

    set ansi_nulls off

    SELECT CASE COL1 WHEN NULL THEN 'Is Null'

    WHEN '0' THEN 'Is Zero'

    WHEN '1' THEN 'Is One'

    WHEN '2' THEN 'Is Two'

    END

    FROM

    (

    SELECT NULL AS Col1

    UNION

    SELECT '0' AS Col1

    UNION

    SELECT '1' AS Col1

    UNION

    SELECT '2' AS Col1

    ) TMP

    statement 2 -

    set ansi_nulls on

    SELECT CASE COL1 WHEN NULL THEN 'Is Null'

    WHEN '0' THEN 'Is Zero'

    WHEN '1' THEN 'Is One'

    WHEN '2' THEN 'Is Two'

    END

    FROM

    (

    SELECT NULL AS Col1

    UNION

    SELECT '0' AS Col1

    UNION

    SELECT '1' AS Col1

    UNION

    SELECT '2' AS Col1

    ) TMP

    Regards Deepak

  • L' Eomot Inversé (10/10/2011)


    Good question.

    But the real answer of course is "it depends" on what the setting of ansi_nulls is; it's on by default in an SSMS query window, so the given answer is correct there; it is incorrect in a context where ansi_nulls is off, which is the default for connections where the application or the connection provider doesn't explicitly it in the connection dialog (SMSS does, SQL 2000 QA did, but some things don't). Fortunately the ansi_nulls setting is an issue which will go away - the ability to change this setting it is a deprecated feature, there are important features which don't work if it is off (indexed views, indexes on computed columns, query notifications), and there will be a new version of SQL Server in which ansi_nulls is effectively always on and can't be switched off.

    One more vote for Toms explanation here.

  • unfortunately i got it wrong. But got something new:-)

  • Good Question.Thanks

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • Thanks L' Eomot Inversé for pointing it out. I overlook this while putting the explanation.

  • I wanted a non of the above answer since the single quotes would not print out.

Viewing 9 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply