How to Delete Records in SQL Using JOIN
Delete with JOIN
Write a SQL query to delete records from one table based on a condition derived from a related table using a JOIN.
Solution:
-- Delete employees who belong to a department that has been marked as inactive.
DELETE E -- Alias for the "Employees" table.
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.DepartmentID -- Join with the "Departments" table.
WHERE D.IsActive = 0; -- Delete employees in inactive departments.
Explanation:
- The goal is to delete all employees who belong to departments marked as inactive (IsActive = 0).
- This demonstrates how to use a DELETE statement with a JOIN to filter rows based on data in a related table.
- DELETE E : Specifies that rows will be deleted from the Employees table (aliased as E).
- FROM Employees E JOIN Departments D ON E.DepartmentID = D.DepartmentID : Combines the Employees table with the Departments table based on the shared column DepartmentID.
- WHERE D.IsActive = 0 : Filters only those employees whose department is marked as inactive.
- A JOIN allows you to reference data from another table to determine which records to delete.
- This approach is useful when the deletion condition involves relationships between tables.
- For example, in a company database, you might use this query to remove all employees from departments that have been deactivated or closed.
1. Purpose of the Query :
2. Key Components :
3. Why use a JOIN? :
4. Real-World Application :
Additional Notes:
- JOIN operations are essential for working with normalized data, where information is split across multiple related tables.
- Scenarios where deleting with a JOIN is appropriate, such as:
- Removing records associated with deactivated or obsolete entities (e.g., inactive departments).
- Cleaning up data based on relationships between tables.
- Important Considerations :
- Ensure that the JOIN condition correctly identifies the relationship between the tables.
- Test the query on a small dataset or in a development environment before applying it to production data.
- Be cautious when deleting records, as this operation is irreversible unless backups exist.
For more Practice: Solve these Related Problems:
- Write a SQL query to delete records from the `orders` table where the `customer_id` matches inactive customers in the `customers` table using a JOIN.
- Write a SQL query to delete records from the `inventory` table where the `product_id` matches discontinued products in the `products` table using a JOIN.
- Write a SQL query to delete records from the `employees` table where the `department_id` matches closed departments in the `departments` table using a JOIN.
- Write a SQL query to delete records from the `transactions` table where the `account_id` matches accounts marked as inactive in the `accounts` table using a JOIN.
Go to:
PREV : Update and Reset Command
NEXT : Optimize SQL Performance with Query Tuning Exercises Home.Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.