Join Predicate

Question

Join Predicate

Viewing 14 posts - 16 through 28 (of 28 total)

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

Strommy SSCrazy Points: 2386 More actions · Answer 1

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

Eric

TA-472286 SSC-Addicted Points: 462 More actions · Answer 2

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 SSCertifiable Points: 6193 More actions · Answer 3

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

"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 SSChasing Mays Points: 616 More actions · Answer 4

Iordan Slavov

SSChasing Mays

Points: 616

December 13, 2007 at 2:14 pm

#760019

Strommy :laugh:

I like the "random join" the most.

Cliff Jones SSChampion Points: 10547 More actions · Answer 5

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.

Mauve SSChampion Points: 11316 More actions · Answer 6

The code shows a clear cut example of why columns in a query should always be qualified. I.e., ALWAYS use aliases!

I got burnt badly with a DELETE statement in a trigger that was using an unqualified reference to the "DELETED" table with the end result being that more rows than what was intended was being deleted.

Using an alias on the table and then qualifing the column will shake out any coding errors, typos, etc. during the parsing phase of the query execution.

Example delete trigger:

DELETE FROM Foo

WHERE FooBar IN (SELECT d.FooBar FROM deleted d)

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

Tom Garth SSCertifiable Points: 6193 More actions · Answer 7

I don't know about the unqualified reference, but I do know that the 'deleted' temp table frequently contains rows that are NOT really being deleted, like during updates.

Why wouldn't you have used a foreign key with cascaded deletes instead?

Tom

Tom Garth
Vertical Solutions[/url]

"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

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 8

Tejinder Arora (12/13/2007)
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'."

The only way to get this error is to put an alias on the Customer table. If you use the query as written, 'MIDDLE' is taken as the alias name and no error occurs.

Derek

Mauve SSChampion Points: 11316 More actions · Answer 9

tgarth (12/14/2007)
Why wouldn't you have used a foreign key with cascaded deletes instead?

I generally do. Triggers are only an exception to the rule. There are certain times when a trigger is necessary due to the application and/or the data model.

Examples:

- SQL Server 2000 doesn't support ON DELETE SET NULL

- Often times in a hierarchical structure (i.e., parent -> child relationships) you'll get burnt as the FK constraint is checked at each row deletion. SQL Server doesn't support "deferrable" (check at the end of the transaction) constraint checking like Oracle does.

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

HardCoder Ten Centuries Points: 1045 More actions · Answer 10

Nice twisted question, people had to think once again, i get it wrong although i was knowing that no middle join is there, but do not beyond that it can be alias...

Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com