Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NULL Values and Joins


NULL Values and Joins

Author
Message
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16441 Visits: 13202
SanDroid (12/20/2010)
I agree that if you read the question the answer is obvious.

However there is a HUGE typo in the explination...
B not C is the Answer?

Disapointing.

May I ask you a question:
why is the first post in this thread from you? (announcing which question this thread belong to).
I was under the impression that you made the question...



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1410 Visits: 1046
da-zero (12/20/2010)
SanDroid (12/20/2010)

However there is a HUGE typo in the explination...
B not C is the Answer?
Disapointing.

May I ask you a question:
why is the first post in this thread from you? (announcing which question this thread belong to).
I was under the impression that you made the question...

Sorry I should have worded this reply better.

The disapointing thing is that this question is eaxactly like it was originaly submitted.
It was edited twice for errors after that, but the corrected version is not what was posted today.

At least the answers and the script match, even if the explination does not.

I took the time to edit and correct my question so it was easy to read, and have a good explination.
It would have been nice to have seen that posted, and not the original with mistakes.

UPDATE: I sent Steve an email explaining to him what happend. I asked that everyone get a free point for having to deal with such things on a Monday.
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 72512
The answer has the following text in it.

7 rows where column d = three

The wording of the answers were confusing as hell.

They should be listed as
7 rows returned, the last row has column d = three

or something like that.

I got it right, but I spent more time analyzing the answers trying to figure out what they meant than I did actually analyzing the question. I kept looking at the code, and saying to myself, "None of the rows repeat enough time to get the word three seven times, etc etc...

I ruled out the insert error because the error that comes back is about inserting into column a, not b.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 72512
Carlo Romagnano (12/20/2010)
philip.cullingworth (12/20/2010)
da-zero (12/20/2010)

Your output has 7 rows and the column d contains "three" for the 7th row, so it should be answer D.


But Answer D is
7 rows where column d = three

I only have 1 row where column d = three Ermm

Please, read carefully the question: it asks how many rows are in the results and what is the value of column "d" on the last row.


Yes, and the answer, the correct answer, states in a sentance that there will be 7 rows where the value of column d will be three.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1410 Visits: 1046
mtassin (12/20/2010)

They should be listed as
7 rows returned, the last row has column d = three

or something like that.


You are absolutely right about that.
cengland0
cengland0
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1484 Visits: 1300

a   b   c   d
4   join1   4   join2
4   join1   4   join2
2   join3   2   one
2   join3   2   one
1   one   NULL   NULL
NULL   NULL   NULL   two
NULL   NULL   NULL   three


The reason I missed the answer is that I thought if you order by column d, "two" would be at the bottom. Doesn't "two" come after "three" in an order clause?

Well, I ignored the "desc" after the order by. Darn it. Details, details.
Bradley Deem
Bradley Deem
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 1248
Carlo Romagnano (12/20/2010)
ian.grace (12/20/2010)
I don't understand how the first transaction could succeed given the attempt to insert the null value into the not-null field "a". Also, why is the last insert of the second transaction, VALUES(2 ,'one'), invalid? Help, I'm confused.

As posted above by vk-kirov and others, the explanation is terrible with a lot of mistakes.



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


SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21073 Visits: 18259
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

jts2013
jts2013
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 5009
A confusing question, with a confusing explanation!
I read it several times and couldn't work out if it was a true test or some kind of trick ;-)
So I didn't learn anything today :-(
Enigma475
Enigma475
SSC Eights!
SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)SSC Eights! (929 reputation)

Group: General Forum Members
Points: 929 Visits: 200
The question was very good! I almost answered it wrong because I thought it was left outer join instead of FULL outer join. For some reason the explanation does not match the correct answer.
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