Traditionally, when performing UPDATE or DELETE operations involving multiple tables, developers often resort to subqueries to establish the necessary relationships between tables. Oracle Database 23c simplifies this process by introducing direct join support for UPDATE and DELETE statements, enabling a more straightforward and intuitive way to express complex operations.
Key Advantages:
Simplified Syntax:
With Direct Joins, the syntax for UPDATE and DELETE statements becomes more concise and readable. Developers can specify join conditions directly in the statement, reducing the need for nested subqueries.
Improved Performance:
I believe that the elimination of subqueries may result in more efficient execution plans. However, in the simple test cases that I performed, I did not notice improvement in the execution plans generated by the new syntax. It might be beneficial in more complex cases.
Enhanced Maintainability:
The streamlined syntax enhances code maintainability by making it easier to understand and modify.
Example:
Consider a scenario where you want to update the salary of employees in a specific department using a direct join.
In a pre-23c Oracle Database version, you would write the UPDATE statement as follows:
-- in pre-23c
UPDATE employees e
SET e.salary = e.salary * 1.1
WHERE e.department_id IN (SELECT d.department_id
FROM departments d
WHERE d.department_name = 'IT');Using direct join in Oracle database 23c, you can achieve the same task using the following statement:
-- Update employee salaries for a specific department using Direct Joins
UPDATE employees e
SET e.salary = e.salary * 1.1
FROM departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'IT';In this example, the UPDATE statement directly specifies the join condition between the “employees” and “departments” tables, making the query more straightforward and eliminating the need for a subquery.
For more information, you can check the UPDATE statement reference documentation on the following link