My friend wanted to use IN command to check the value from a table used in an outer join but Oracle does not allow the usage of IN command for tables connected by outer join. I used regular expressions to overcome this.
Here is an example below with 2 tables (Main and Sometime):
a. Table Main with 1 column 'ID'
b. Table Sometime with 2 columns, ID and Status
SELECT m.id , s.status
FROM main m, sometime s
WHERE m.id = s.id (+)
AND REGEXP_LIKE (s.status(+), 'ALIVE|DEAD');
The SQL query above would return the following result: