DELETE Syntax Breakdown:

Basic DELETE:

DELETE FROM table_name WHERE condition;

Components:

- DELETE FROM: Keyword to remove rows

- table_name: Table to delete from

- WHERE condition: Filter which rows to delete (CRITICAL - without WHERE, deletes all rows)

Examples:

DELETE FROM orders WHERE order_date < '2023-01-01';

DELETE FROM customers WHERE email IS NULL;

DELETE FROM products WHERE stock = 0 AND discontinued = true;

DELETE with JOIN (database-specific):

PostgreSQL:

DELETE FROM orders o

USING customers c

WHERE o.customer_id = c.customer_id

AND c.status = 'inactive';

MySQL:

DELETE o FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

WHERE c.status = 'inactive';

SQL Server:

DELETE o

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

WHERE c.status = 'inactive';

DELETE with Subquery (portable):

DELETE FROM orders

WHERE customer_id IN (

SELECT customer_id FROM customers WHERE status = 'inactive'

);

DELETE with RETURNING (PostgreSQL):

DELETE FROM orders WHERE order_date < '2023-01-01'

RETURNING order_id, customer_id, total;

-- Returns deleted rows (useful for audit logging)

DELETE with OUTPUT (SQL Server):

DELETE FROM orders

OUTPUT DELETED.order_id, DELETED.customer_id, DELETED.total

WHERE order_date < '2023-01-01';

-- Returns deleted rows