SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NULL Values and Joins


NULL Values and Joins

Author
Message
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26689 Visits: 12512
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
malleswarareddy_m
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2795 Visits: 1189
I got to wrong by misunderstanding the answer.

Malleswarareddy
I.T.Analyst
MCITP(70-451)
ian.grace
ian.grace
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
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
volter_ddun-856145
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 71
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
volter_ddun-856145
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 71
same result i got:-)
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7700 Visits: 3401
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.

I run on tuttopodismo
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19325 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SQLZealot
SQLZealot
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 28
Smile 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
UMG Developer
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4184 Visits: 2204
Thanks for the question, and the explanation of why the explanation seemed so weird.
idiras17
idiras17
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 3
why you give me that without beeing before responding
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search