如何将左自外连接查询转换为Oracle (+)语法实现？

Got it, let's convert your ANSI-style left self-join to Oracle's legacy (+) syntax. Here's how to do it step by step:

First, let's assume your original working query looks something like this (swap in your actual table name and join columns):

SELECT
    main.trans_id,
    main.trans_type,
    main.other_columns,
    ud.user_id
FROM
    your_transaction_table main
LEFT OUTER JOIN
    your_transaction_table ud
    ON main.trans_id = ud.trans_id  -- Use your actual join key(s) here
    AND ud.trans_type = 'UPDATE DETAIL';

This query keeps all rows from the main table, and only fills the user_id column when there's a matching row in the joined table where trans_type is 'UPDATE DETAIL'—exactly what you need.

Now, here's the equivalent using Oracle's (+) syntax:

SELECT
    main.trans_id,
    main.trans_type,
    main.other_columns,
    ud.user_id
FROM
    your_transaction_table main,
    your_transaction_table ud
WHERE
    main.trans_id = ud.trans_id (+)  -- Add (+) to the optional table's column
    AND ud.trans_type (+) = 'UPDATE DETAIL';  -- Include (+) here too for the filter on the joined table

A few key things to remember:

• Move both tables into the FROM clause separated by commas.
• For every condition involving the "optional" table (the one that might not have a match, which is ud here), append (+) to its column. This tells Oracle it's an outer join.
• Any filters that apply specifically to the optional table (like checking trans_type = 'UPDATE DETAIL') must also include the (+)—if you leave it off, it becomes a post-join filter that would exclude rows where there's no match, which breaks the left outer join behavior.

If your original query uses multiple join columns (like trans_id and transaction_date), just add (+) to each of the optional table's columns in those conditions:

WHERE
    main.trans_id = ud.trans_id (+)
    AND main.transaction_date = ud.transaction_date (+)
    AND ud.trans_type (+) = 'UPDATE DETAIL';

This will behave exactly like your original query—you'll get all rows from your table, with user_id populated only for rows where there's a corresponding 'UPDATE DETAIL' entry.

内容的提问来源于stack exchange，提问作者M. Doe