Wednesday, September 23, 2009

Hacking Oracle Outer Join

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'
ID
10
20
30

b. Table Sometime with 2 columns, ID and Status
ID STATUS
20 ALIVE
10 DEAD



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:

ID STATUS
20 ALIVE
10 DEAD
30