SQL DELETE with JOIN

Remove products that have not sold.

DELETE P
  FROM Product P
  LEFT JOIN OrderItem I ON P.Id = I.ProductId
 WHERE I.Id IS NULL

This problem requires a LEFT JOIN.
P and I are table aliases.

JOIN syntax.

DELETE table-name1
  FROM table-name1 
  JOIN table-name2 ON column-name3 = column-name4
 WHERE condition

INNER JOIN syntax.

DELETE table-name1
  FROM table-name1 
 INNER JOIN table-name2 ON column-name3 = column-name4
 WHERE condition

JOIN is the same as INNER JOIN; the INNER keyword is optional.

DELETE OrderItem
  FROM OrderItem
  JOIN [Order] on OrderItem.OrderId = [Order].Id
  JOIN Product ON Product.Id = OrderItem.ProductId
 WHERE OrderNumber = '542379' 
   AND ProductName = 'Tofu'

Note: This removes a single order item. With that, the TotalAmount value in the Order table is now invalid. To recalculate this value requires a separate UPDATE query.

• Our Sql DELETE Tutorial
• Our Sql UPDATE JOIN Tutorial
• Our Sql JOIN 3 Tables Tutorial
• Our Sql JOIN Multiple Tables Tutorial