And now, the last operation with JOIN: DELETE.

The query below removes orders where the product's delivery date is 2017 or earlier:

DELETE Purchase 
FROM Purchase 
JOIN Product ON Purchase.ProductId = Product.Id 
WHERE YEAR(Product.DeliveredDateTime) <= 2017;

In a simple DELETE query, we place the name of the table from which we'll remove records after the DELETE keyword. If we're joining another table, we put another FROM clause with the same table name (again) and JOIN, followed by the name of the joining table. Next, we have ON and the join conditions (Purchase.ProductId = Product.Id). Notice that we use the same table after both DELETE and FROM.

Another way you can write this query is:

DELETE FROM Purchase 
FROM Purchase 
JOIN Product ON Purchase.ProductId = Product.Id 
WHERE YEAR(Product.DeliveredDateTime) <= 2017;