Subqueries can be used in the following situations:

1. After an IN keyword
2. After a NOT IN keyword
3. After an EXISTS keyword
4. After a NOT EXISTS keyword
5. After an ANY keyword
6. After a SOME keyword
7. After an ALL keyword
8. When comparing values
9. Where any expression can be used

A subquery is most commonly used in place of joining two tables together. Generally, there is no performance difference between using joins with multiple tables or using subqueries, with one exception: if the subquery contains an EXISTS clause, it would be preferable to construct a join.

DELETE FROM table
[FROM table [,…]]
WHERE column [NOT] IN|[NOT] 
EXISTS|ANY|SOME|ALL|Comparison (subquery)

The important thing to remember when using a subquery is that the subquery will return one or more values. You can determine if the whole query might fail by first executing the subqueries individually. Doing so will let you understand the values that are returned by each subquery. Then, it might be a good idea to include those values into the outermost query by using literals. This way, it makes your complicated queries manageable.
You can use a subquery anywhere that an expression is required within a DELETE Transact-SQL statement’s WHERE clause.