PL/SQL DELETE JOIN
In database management, removing specific records from a table is a common task especially when cleaning or updating data. One useful technique is the DELETE JOIN which allows us to delete records from one table based on conditions involving another table.
In this article, we will explore PL/SQL DELETE JOIN by understanding its syntax and working through practical examples.
PL/SQL DELETE JOIN
A DELETE JOIN in PL/SQL is used to delete rows from one table based on a condition that involves another related table. This technique combines the delete operation with a join and making it efficient for removing records across multiple tables in a relational database.
Syntax:
DELETE FROM left_table
WHERE left_table.column_name IN (
SELECT left_table.column_name
FROM left_table
JOIN right_table
ON left_table.column_name = right_table.column_name
WHERE <conditions>);
Explanation:
- DELETE FROM left_table: Specifies that rows will be deleted from the left table.
- WHERE clause: Filters the rows that will be deleted based on conditions defined by the join.
- JOIN right_table: The right table is used to define the relationship for the condition, typically based on a foreign key.
- <conditions>: Additional conditions to specify which rows should be deleted.
Examples of PL/SQL DELETE JOIN
In this section, we'll create two tables, Products
and Orders
and demonstrate how to use a DELETE JOIN in PL/SQL to delete records from the Products
table based on conditions involving the Orders
table.
Example 1: Deleting Products Without Orders
In this example, we create two tables: Products
and Orders
. Our goal is to delete all products that do not have any associated orders.
Step 1: Create the Products Table
We start by creating a Products
table with product_id
and product_name
columns.
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR2(50)
);
INSERT INTO Products (product_id, product_name)
VALUES (1, 'Laptop');
INSERT INTO Products (product_id, product_name)
VALUES (2, 'Phone');
INSERT INTO Products (product_id, product_name)
VALUES (3, 'Tablet');
Output:
product_id | product_name |
---|---|
1 | Laptop |
2 | Phone |
3 | Tablet |
Explanation:
- The
Products
table contains three products: a laptop, a phone, and a tablet.
Step 2: Create the Orders Table
Next, we create an Orders
table that records which products have been ordered.
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
product_id INT,
order_quantity INT
);
INSERT INTO Orders (order_id, product_id, order_quantity)
VALUES (101, 1, 2);
INSERT INTO Orders (order_id, product_id, order_quantity)
VALUES (102, 2, 1);
Output:
order_id | product_id | order_quantity |
---|---|---|
101 | 1 | 2 |
102 | 2 | 1 |
Explanation:
- The
Orders
table contains two orders: one for a laptop and one for a phone. - Notice that the
Tablet
in theProducts
table does not have a corresponding order.
Step 3: Use DELETE JOIN to Remove Products Without Orders
Now, we will delete products from the Products
table that do not have any associated orders (i.e., the Tablet
).
DELETE FROM Products
WHERE product_id IN (
SELECT p.product_id
FROM Products p
LEFT JOIN Orders o
ON p.product_id = o.product_id
WHERE o.order_id IS NULL
);
Output:
product_id | product_name |
---|---|
1 | Laptop |
2 | Phone |
Explanation:
- The
LEFT JOIN
identifies products without corresponding orders in theOrders
table by checking whereorder_id
isNULL
. - The
Tablet
(product_id 3) is deleted because it does not have any orders.
Example 2: Deleting Orders for Specific Products
In this example, we want to delete all orders for a specific product (e.g., Phone
) from the Orders
table.
Query:
DELETE FROM Orders
WHERE product_id = (
SELECT product_id
FROM Products
WHERE product_name = 'Phone'
);
Output:
order_id | product_id | order_quantity |
---|---|---|
101 | 1 | 2 |
Explanation:
- The
DELETE
operation removes all orders where theproduct_id
matches thePhone
. - After executing the query, only the order for the
Laptop
remains in theOrders
table.
Conclusion
In this article, we explored how to use PL/SQL DELETE JOIN to efficiently delete records from one table based on conditions involving another table. This technique is powerful for maintaining database integrity and ensuring that data is kept up to date.