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

DELETE grandchildren - use WHERE EXISTS or JOIN? Expand / Collapse
Author
Message
Posted Tuesday, February 2, 2010 12:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:41 AM
Points: 275, Visits: 1,103
So someone pointed out an interesting discussion they ran across and now I was wondering about it:

DELETE grandchild
FROM grandchild gc
JOIN child c on c.child_id=gc.child_id
join parent p on p.parent = c.parent_id

vs.

DELETE grandchild gc
WHERE EXISTS
(SELECT *
FROM child c
JOIN parent p
ON c.parent_id = p.parent_id
WHERE gc.child_id = c.child_id
)


-crappy formatting there I know

Anyway, why would you use EXISTS over the more direct join?
Post #858194
Posted Tuesday, February 2, 2010 3:25 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 @ 10:01 AM
Points: 3,844, Visits: 3,841
Your question is one that is often asked and debated. Check out this link:

http://www.sqlservercentral.com/Forums/Topic848551-360-1.aspx?Highlight=%22my+first+article%22




John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #858281
Posted Wednesday, February 3, 2010 12:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
Because the join isn't necessary. All you're trying to do is find rows that exist in the other table. So why not use EXISTS?

One of these may have been the 'interesting discussion'

http://sqlinthewild.co.za/index.php/2010/01/12/in-vs-inner-join/
http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #858422
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse