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


Join Predicate


Join Predicate

Author
Message
Simon Facer
Simon Facer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

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



StarNamer
StarNamer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 1992
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 Smile

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
Iordan Slavov
Iordan Slavov
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
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?



Adrian Nichols-360275
Adrian Nichols-360275
SSC Eights!
SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)SSC Eights! (814 reputation)

Group: General Forum Members
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...
StarNamer
StarNamer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 1992
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! Wink

Derek
Strommy
Strommy
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
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. BigGrin

Eric



TA-472286
TA-472286
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
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'."
Tom Garth
Tom Garth
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1013 Visits: 1499
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

Iordan Slavov
Iordan Slavov
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 44
Strommy Laugh
I like the "random join" the most.



Cliff Jones
Cliff Jones
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: 4069 Visits: 3648
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.
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