? DELETE with JOIN in SQL

Sometimes you want to delete rows from a table based on matching conditions in another table. This is done using a DELETE with JOIN.

? Syntax Examples by SQL Dialect

1. MySQL

DELETE t1 FROM table1 t1JOIN table2 t2 ON t1.common_field = t2.common_fieldWHERE t2.some_condition = 'value';

• Deletes rows from table1 where join condition and WHERE clause match.

2. SQL Server

DELETE t1FROM table1 t1JOIN table2 t2 ON t1.common_field = t2.common_fieldWHERE t2.some_condition = 'value';

3. PostgreSQL

PostgreSQL does not support DELETE with JOIN directly, but you can use a USING clause:

DELETE FROM table1 t1USING table2 t2WHERE t1.common_field = t2.common_field  AND t2.some_condition = 'value';

? Example Scenario

Delete employees who work in a department marked inactive

DELETE eFROM employees eJOIN departments d ON e.department_id = d.idWHERE d.status = 'inactive';

? Important Notes

• Be careful with join conditions to avoid deleting unintended rows.

• Always run a SELECT with the same join and conditions first to check what rows will be deleted.

• Syntax varies slightly between DBMS, so check your database documentation.

If you want, I can tailor the DELETE JOIN query for your specific SQL database!