Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Join Predicate Expand / Collapse
Author
Message
Posted Thursday, December 13, 2007 8:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:36 PM
Points: 1,568, Visits: 670
Well, there's 5 minutes of my life I'll never get back! This question has nothing to do with how much you know about T-SQL, just do you pay attention when you are reading bad code. This reminds me of the style of Microsoft certification questions from the 1990's. Lets leave the trick questions where they belong - anywhere else but on this site, or preferably nowhere at all.


Post #432864
Posted Thursday, December 13, 2007 8:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
Pete Brown (12/13/2007)
I wonder what benefit questions like these have to the community at large.
I thought the question of the day was there to inform not just to make the author feel smug!!!

The point is that you have to look at what the code does, especially if the syntax looks new to you.

All the following work, and are all equivalent, even though a few get highlighted strangely
SELECT *  FROM dbo.Customers
PARTIAL JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
CONNECTED JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
DISCONNECTED JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
UPPER JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
LOWER JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
INVERTED JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
EQUAL JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
UNEQUAL JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
TRIANGULAR JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
INVISIBLE JOIN dbo.Employees ON CustomerID = EmployeeID

And, of course, all these work, but one returns a different result to the others :)
SELECT *  FROM dbo.Customers
RIHGT JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
RITE JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
WRITE JOIN dbo.Employees ON CustomerID = EmployeeID
SELECT * FROM dbo.Customers
RIGHT JOIN dbo.Employees ON CustomerID = EmployeeID



Derek
Post #432883
Posted Thursday, December 13, 2007 8:41 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 4:03 PM
Points: 68, Visits: 44
That's a trick question, alright. But don't get so upset you didn't answer it correctly - I didn't either. My thoughts went something like "A predicate for joins that I don't know? Possible but very unlikely... Must be a code error". I didn't question the syntax rules ... But if they allow you to write "bad" code may be the rules are bad? Had the "as" word been mandatory you couldn't be tricked - after all how much of a saving are 2 letters?




Post #432895
Posted Thursday, December 13, 2007 8:42 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 814, Visits: 915
I think that the question is quite useful, especially if you are starting a new DBA job or contract; poorly formatted coding is out there. The author may well feel smug as there are a number of people (including myself) who did not read the code properly and chose an incorrect answer.

Life is full of little hurdles that will trip you up; just don't let a slightly damaged ego cloud any further judgement.




Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
Post #432896
Posted Thursday, December 13, 2007 9:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
Iordan Slavov (12/13/2007)
Had the "as" word been mandatory you couldn't be tricked - after all how much of a saving are 2 letters?

That's what I felt when I wasn't getting an expected result and finally noticed the typo in a RIHGT JOIN! ;)


Derek
Post #432930
Posted Thursday, December 13, 2007 9:29 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 20, 2012 12:51 PM
Points: 322, Visits: 351
I heard Microsoft is implementing middle joins in 2008. It's for those indiscriminate coders that aren't sure what type of join they want to use. Now you don't have to choose, you can compromise! Next for SQL 2011, the random join, for those who just don't care...

Let's get some useful questions. If I saw a programmer put MIDDLE JOIN in their code, I would be tempted to smack them with my keyboard. :D

Eric



Post #432939
Posted Thursday, December 13, 2007 11:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 8, 2009 4:34 PM
Points: 204, Visits: 58
The answer you have posted is wrong
I tried it on SQL server and it says
"Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MIDDLE'."
Post #433026
Posted Thursday, December 13, 2007 12:01 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
At the bottom of the Question / Answer page is a dropdown allowing us to specify how many questions to display on the page. Can we get a number lower than 10?

All those RED icons are beginning to depress me.

Tom Garth


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #433027
Posted Thursday, December 13, 2007 2:14 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 4:03 PM
Points: 68, Visits: 44
Strommy
I like the "random join" the most.





Post #433073
Posted Thursday, December 13, 2007 2:24 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:27 PM
Points: 3,901, Visits: 3,634
Had you written the question like this I probably would have gotten it right.

SELECT * FROM dbo.Customers MIDDLE
JOIN dbo.Employees ON CustomerID=EmployeeID

I guess I did learn that indentation improves readability but I am not sure it was worth the effort to learn what I thiink I already knew.

Should not use "Select *" though.
Post #433076
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse