Correlated DELETE with Subquery
Correlated DELETE with Subquery is a SQL statement in the Subqueries category. Deletes rows based on conditions in a correlated subquery referencing another table. The syntax is DELETE FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.col = table1.col AND condition);. It returns rows deleted. A typical example: DELETE FROM orders o WHERE EXISTS ( SELECT 1 FROM customers c WHERE c.id = o.customer_id AND c.status = 'closed' ); -- Delete all orders from closed customer accounts A close relative is Scalar Subquery, which a subquery that returns a single value (one row, one column). Can be used in SELECT, WHERE, HAVING clauses. A close relative is Correlated Subquery, which a subquery that references columns from the outer query. Evaluated for each row of the outer query. A close relative is EXISTS, which returns TRUE if the subquery returns at least one row. Often faster than IN for large datasets.