NULL Values and Joins

Question

Post reply

NULL Values and Joins

Viewing 15 posts - 16 through 30 (of 41 total)

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

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

ian.grace

SSC Rookie

Points: 35

December 20, 2010 at 4:14 am

#1264556

Thanks for that

Marek Grzymala SSCommitted Points: 1946 More actions · Answer 2

so the correct answer should be: "7 rows where the last row's column d = three", not: "7 rows where column d = three", going by the way the question is phrased none of the answers are correct.

sjimmo SSChampion Points: 11139 More actions · Answer 3

Good Monday morning question - math test and logic while clearing the cobwebs and trying to get my first cup of coffee;-)

Made me think, question and reread.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

ronmoses@gmail.com SSCarpal Tunnel Points: 4480 More actions · Answer 4

I'm completely confused by this explanation. Especially this part:

However, since the last insert in the second Transaction was invalid, no rows were inserted into table2. So B not C is the correct answer.

But D is given as the correct answer, and as far as I can tell, rows are inserted into table2. What am I missing?

Ron

-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown

SanDroid SSChampion Points: 10068 More actions · Answer 5

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.

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 6

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...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SanDroid SSChampion Points: 10068 More actions · Answer 7

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 SSC-Insane Points: 23099 More actions · Answer 8

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[/url]
For tips on how to post your problems[/url]

mtassin SSC-Insane Points: 23099 More actions · Answer 9

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[/url]
For tips on how to post your problems[/url]

SanDroid SSChampion Points: 10068 More actions · Answer 10

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 SSCertifiable Points: 6102 More actions · Answer 11

abcd

4join14join2

2join32one

1oneNULLNULL

NULLNULLNULLtwo

NULLNULLNULLthree

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 SSCrazy Points: 2565 More actions · Answer 12

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 SSC Guru Points: 281334 More actions · Answer 13

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[/url]
Learn Extended Events

jts2013 Hall of Fame Points: 3226 More actions · Answer 14

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 Ten Centuries Points: 1247 More actions · Answer 15

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.