SQL DELETE with JOIN

SQL DELETE JOIN Explained

A SQL DELETE statement can include JOIN operations.

It can contain zero, one, or multiple JOINs.

The DELETE removes records that satisfy the JOIN conditions.

Example

#

Problem: Remove products that have not sold.

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

Note: this particular problem requires a LEFT JOIN.
However, JOIN is also perfectly valid in a DELETE statement.

Syntax

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.
INNER JOIN is the most commonly used type of JOIN operation.


More Examples

SQL DELETE with JOIN

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity
Problem: For order number 542379 remove the Tofu.
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: an order item was removed.
To update the TotalAmount in the Order table will need a separate UPDATE query.

Result:  1 record removed.

You may also like



Guides