SQL DELETE JOIN
Syntax
DELETE FROM table_name1
JOIN table_name2 ON join_condition
WHERE where_condition;
Example
Suppose we have two tables - customers and orders, with the following structures:
customers
| customer_id | customer_name | city |
|---|---|---|
| 1 | John | LA |
| 2 | Jane | NY |
| 3 | Mark | LA |
| 4 | Mike | SF |
orders
| order_id | customer_id | product |
|---|---|---|
| 1 | 1 | shoes |
| 2 | 1 | socks |
| 3 | 3 | shirt |
| 4 | 4 | hat |
To delete all orders for customers located in LA, we could use the following DELETE JOIN query:
DELETE FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.city = 'LA';
Output
After executing the above query, the orders table will contain only the following records:
| order_id | customer_id | product |
|---|---|---|
| 4 | 4 | hat |
Explanation
In SQL, JOIN is used to combine data from two or more tables based on a related column between them. Using a DELETE JOIN statement allows you to delete records from one or more tables based on a common condition specified in the WHERE clause.
In the above example, we are joining the orders table with the customers table on the customer_id column, and then deleting all records from the orders table where the customer's city is LA.
Use
DELETE JOIN is useful when you need to delete records from multiple tables that are related to each other. For example, if you have a database of customers and orders, and you need to delete all orders for a specific customer, you can use a DELETE JOIN statement to delete records from both the orders and customers tables at the same time.
Important Points
Be careful when using DELETE JOIN, as it can permanently delete data from your tables.
- Always make a backup of your data before performing any DELETE operations.
- Make sure to specify a WHERE condition to avoid accidentally deleting all records from your tables.
Summary
- DELETE JOIN is used to delete records from one or more tables based on a condition using the JOIN operator.
- The syntax is similar to a regular DELETE statement, but also includes a JOIN clause.
- Always be careful when using DELETE JOIN, and make sure to specify a WHERE condition to limit the scope of the deletion.