NULL Values and Joins

Question

NULL Values and Joins

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

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

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 More actions · Answer 2

I got to wrong by misunderstanding the answer.

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

ian.grace SSC Rookie Points: 35 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

volter_ddun-856145

Grasshopper

Points: 21

December 23, 2010 at 12:31 am

#1265796

same result i got:-)

Carlo Romagnano SSC-Insane Points: 22662 More actions · Answer 6

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: 64790 More actions · Answer 7

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 More actions · Answer 8

🙂 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 More actions · Answer 9

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

idiras17 Grasshopper Points: 12 More actions · Answer 10

why you give me that without beeing before responding

idiras17 Grasshopper Points: 12 More actions · Answer 11

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: 557 More actions · Answer 12

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.