NULL Values and Joins

  • TomThomson

    SSC Guru

    Points: 104772

    Seth Kramer (12/18/2010)


    I accept I got it wrong, but what do you mean the second transaction fails? Only the insert null into the primary key field statement fails. There are no failed inserts on Table2.

    It looks to me as if the explanation paragraph following the reference to BoL has got sucked in from some other explanation, as it certainly has no connection with this question. Also, the wording of the possible answers is horrible and could easily have been fixed by changing "where" to "last" and sticking a comma in front of it.

    At first I thought I would have to rack my brains and remember how nulls are treated by ORDER BY, but then I noticed that there were only two options with 7 rows and neither of them had NULL as the last d value, so the ordering of nulls was irrelevant after all and it was actually a no-brainer question.

    Tom

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    I got to wrong by misunderstanding the answer.

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

  • ian.grace

    SSC Rookie

    Points: 35

    Bradley Deem (12/20/2010)


    Ian, I know you've received an explanation already, but I wanted to add to that. You can control the behavior on error as follows in the comments below.

    BEGIN TRAN

    INSERT INTO [Table1]( a,b)

    VALUES(1 ,'one')

    INSERT INTO [Table1]( a,b) -- Insert fails because the NOT NULL constraint on column [a]

    VALUES(NULL ,'five')

    -- Even though the above insert STATEMENT fails, execution of the BATCH continues.

    -- To halt execution after error use SET XACT_ABORT = ON (stops the BATCH from executing)

    -- To reroute execution after error use TRY CATCH (recommended) or check @@Error and use GOTO

    INSERT INTO [Table1]( a,b)

    VALUES(4 ,'join1')

    INSERT INTO [Table1]( a,b])

    VALUES(2 ,'join3')

    COMMIT TRAN

    GO

    That's a good explanation, thank you. I'm usually a bit lazy with my error handling and use of transactions, hence my confusion.

  • volter_ddun-856145

    Grasshopper

    Points: 21

    first thing it can't insert null value in to Table1 AND after running that query in sqlserver i didn't get result as mentioned Correct answer.

    Correct answer: 7 rows where column d = three

    can any body explain how it is possible?

    thanks

  • volter_ddun-856145

    Grasshopper

    Points: 21

    same result i got:-)

  • Carlo Romagnano

    SSC-Insane

    Points: 21833

    volter_ddun-856145 (12/23/2010)


    first thing it can't insert null value in to Table1 AND after running that query in sqlserver i didn't get result as mentioned Correct answer.

    Correct answer: 7 rows where column d = three

    can any body explain how it is possible?

    thanks

    Please, read the last post of the first page.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    This was an ... interesting question.

    I don't call it a good question, because of the complexity - not the complexity of the subject matter, but that of the number of statements, and the number of rows. We had to deal with behaviour of a failed insert in a transaction, then the behaviour of two kinds of joins, union, ordering, and the position of NULL values in an order by (which, for the record, is not the same in all RDBMS's; whether NULLs go first or last is not defined in the ANSI standard, but left as an implementation-dependant choice). And all that on tables with three or four rows.

    I think a good QotD should test one, maybe two subjects only. And preferably with a combination of statement complexity and number of rows that enables those with an understanding of the subject to work out the results in their head. This one severely pushed my ability to work it out in my head.

    SanDroid, I'd love to see more questions from you, as the idea for this question is great - but as a suggestion for the next time, consider using two or three rows in the tables, not more. And don't add extra complexity by adding weird primary keys, failed inserts, etc. Just giving the table population, a query with a join on nullable columns, and some believable but incorrect answer options would have been enough.

    As to the erroneous explanation - well, you already explained that this is the result of changes to question and explanation getting out of sync. Stuff like that happens, 'nuff said.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • SQLZealot

    Valued Member

    Points: 63

    🙂 don't worry, it happened to me too, I did not read carefully the question. Morale of the story: it is better to pay attention at the requirements and just not knowing the correct answer, than missing the question and answer the wrong thing. However, the explanation they provided, that 'no rows are inserted in the Table2' is completely bizarre. (repairing frozen pipes qualify for not reading carefully)

  • UMG Developer

    SSChampion

    Points: 13482

    Thanks for the question, and the explanation of why the explanation seemed so weird.

  • idiras17

    Grasshopper

    Points: 12

    why you give me that without beeing before responding

  • idiras17

    Grasshopper

    Points: 12

    sir you can help to me and send to my account the total marks just i have untill now

  • vinod.andani-874416

    Mr or Mrs. 500

    Points: 527

    Whoever posts question in QoD column, always makes sure your question is correct with zero syntax and semantic errors and also make sure when you ignore for syntax/semantic error atleast do not put the answer which evaluates according to SQL parser.

    E.g., Error : can not insert NULL in column b. This should be removed from being selected. People who answer the SQL puzzles always first look at the syntax first.

Viewing 12 posts - 31 through 42 (of 42 total)

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