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);