Oracle old Lelft Join syntax (+) usage with the Like operator in the join condition

Question

This is on Oracle 8i (see the note at the bottom of the question).

I am matching keywords on strings. A specific string will result in two or more rows if it matches more than one of the keywords. But I also want to capture the strings that have no match in the results.

Consider the following:

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

This code yields the following:

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

I would like to do a Left Join in order to additionally get this row:

ID  TXT                         KEYWORD
4   I like onions               NULL

However, the following syntax using the old Oracle way of doing a Left Join using (+), i.e. :

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

does not work and throws ORA-00933: SQL command not properly ended

I'm on Oracle 8i so I can't do the ANSI-92 SQL join syntax.

NOTE: Just to be clear, I am perfectly aware that 8i is old and deprecated but I have no control over that.

Answer 1

Outer join operator is at the wrong place; try

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