Emptying a table

Safely remove all rows.

DELETE with JOIN

In some databases you can use DELETE with JOIN to remove rows from related tables.

DELETE p FROM products p
JOIN order_items oi ON p.id = oi.product_id
WHERE p.stock = 0;

This query removes out-of-stock products that were ordered.

Rows are deleted from products. Only the rows where p.stock = 0 holds are affected.

MySQL supports this syntax. PostgreSQL uses a USING clause. The syntax varies by database.

Delete the row with id=3 from products.

Show example solution

DELETE FROM products
WHERE id = 3

Delete with join

DELETE p FROM products p
____ order_items oi ON p.id = oi.product_id;

Show answer

DELETE p FROM products p
JOIN order_items oi ON p.id = oi.product_id;