Join Predicate

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ramesh » Join Predicate

27 posts, Page 3 of 3

«««1 2 3

Join Predicate

Rate Topic

Display Mode

Topic Options

Author

Message

Mauve

Posted Friday, December 14, 2007 7:35 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 10:43 AM
Points: 1,146, Visits: 1,848

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)

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

Post #433344

Tom Garth

Posted Friday, December 14, 2007 7:47 AM

SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499

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

"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 #433349

Derek Dongray

Posted Monday, December 17, 2007 5:31 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

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

Post #433839

Mauve

Posted Monday, December 17, 2007 9:17 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 20, 2013 10:43 AM
Points: 1,146, Visits: 1,848

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.

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

Post #433930

HardCoder

Posted Friday, December 28, 2007 4:24 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 18, 2011 12:57 AM
Points: 213, Visits: 97

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

Post #436982