Friday, August 21, 2015

Deleting records based on another table: Should I use standard delete or non-standard delete statement?

When you have to delete records from a table based on a filter set with another table, what type of statement generally you use? DELETE based on a join or DELETE based on a sub query?

The standard way of deleting records for this requirement is using DELETE based on a sub query. However if you prefer or more familiar with JOINs, it can be used without any issue though it is considered as the non-standard way. SQL Server executes both types of queries in same way, hence we cannot expect any performance difference between these two.

Here is an example. Following code deletes records from SalesOrderDetails table based on Color attribute of Product table. As you see, cost is same and plan is same too.

USE AdventureWorks2014;
GO

-- non-standard way
DELETE FROM d
FROM Sales.SalesOrderDetail d
 JOIN Production.Product p
  ON d.ProductID = p.ProductID
WHERE p.Color IS NULL;

-- standard way
DELETE FROM Sales.SalesOrderDetail
WHERE EXISTS (SELECT *
   FROM Production.Product p
   WHERE Sales.SalesOrderDetail.ProductID = p.ProductID
    AND p.Color IS NULL);


No comments: