Oracle 旧的 Lelft Join 语法 (+) 用法，在连接条件中使用 Like 运算符

Question

这是在Oracle 8i上(请参阅问题底部的注释(。

我正在匹配字符串上的关键字。如果特定字符串与多个关键字匹配，则会导致两行或更多行。但我也想捕获结果中不匹配的字符串。

请考虑以下事项：

SELECT
FREE_TEXT.*,
KEYWORDS.KEYWORD    
FROM
(
(SELECT 1 AS ID, 'I like potatoes' AS TXT FROM DUAL) UNION
(SELECT 2 AS ID, 'I like peppers' AS TXT FROM DUAL) UNION
(SELECT 3 AS ID, 'I like potatoes, peppers' AS TXT FROM DUAL) UNION
(SELECT 4 AS ID, 'I like onions' AS TXT FROM DUAL)
) FREE_TEXT,    
(
(SELECT 1 AS ID, 'potato' AS KEYWORD FROM DUAL) UNION
(SELECT 2 AS ID, 'pepper' AS KEYWORD FROM DUAL) UNION
(SELECT 3 AS ID, 'carrot' AS KEYWORD FROM DUAL)
) KEYWORDS  
WHERE
FREE_TEXT.TXT LIKE ('%' || KEYWORDS.KEYWORD || '%') 
ORDER BY
FREE_TEXT.ID

此代码生成以下内容：

ID  TXT                         KEYWORD
1   I like potatoes             potato
2   I like peppers              pepper
3   I like potatoes, peppers    potato
3   I like potatoes, peppers    pepper

我想做一个左联接，以便额外获得这一行：

ID  TXT                         KEYWORD
4   I like onions               NULL

但是，以下语法使用旧的 Oracle 方式使用 (+( 执行左连接，即：

WHERE FREE_TEXT.TXT LIKE ('%' || KEYWORDS.KEYWORD || '%') (+)

不起作用并扔ORA-00933: SQL command not properly ended

我使用的是Oracle 8i，所以我不能做ANSI-92 SQL联接语法。

注意：需要明确的是，我完全知道 8i 是旧的并且已弃用，但我无法控制它。

Answer 1

外部连接运算符位于错误的位置;请尝试

WHERE FREE_TEXT.TXT LIKE ('%' || KEYWORDS.KEYWORD (+) || '%')