Styjun

Table1

|------------------------------------------------------------|
| Para_1 | column_A | column_B | column_C |
--------------------------------------------------------------
| 3007576 | abc | | |
| 3007879 | ab | fg | |
| 3007880 | ad | | x |
| 3007900 | | | |
|------------------------------------------------------------|

Table2

|------------------------------------------------------------|
| Para_2 | column_A | column_B | column_C |
--------------------------------------------------------------
| 100 | abcd | fgh | xyz |
| 200 | abc | fg | z |
| 300 | ab | g | xy |
|------------------------------------------------------------|

Expected Results:

|------------------------------------------------------------|
| Para_1 | column_A | column_B | column_C | Para_2
--------------------------------------------------------------
| 3007576 | abc | | | 100
| 3007576 | abc | | | 200
| 3007879 | ab | fg | | 100
| 3007879 | ab | fg | | 200
| 3007880 | ad | | x | null
| 3007900 | | | | null
|------------------------------------------------------------|

select table1.*, table2.Para_2, table1.column_A
from table1
left outer join table2
on table2.column_A like ('%'||table1.column_A||'%') 
and table2.column_B like ('%'||table1.column_B||'%') 
and table2.column_C like ('%'||table1.column_C||'%') 

where table1.column_A is not null
and table1.column_B is not null
and table1.column_C is not null

the above code seems not sufficient.. any idea?

remove where clause because in each row in your sample data there is at least one null column value with the 3 column A,B,C so it filtered all

select table1.*, table2.Para_2, table1.column_A
from table1
left outer join table2
on table2.column_A like ('%'||table1.column_A||'%') 
and table2.column_B like ('%'||table1.column_B||'%') 
and table2.column_C like ('%'||table1.column_C||'%')

This produces your expected result:

select table1.*, table2.Para_2
from table1 left outer join table2
on table2.column_A like '%'||table1.column_A||'%' 
and table2.column_B like '%'||table1.column_B||'%'
and table2.column_C like '%'||table1.column_C||'%'
and coalesce(table1.column_a, table1.column_b, table1.column_c) is not null;

As you can see, this is almost what you had. But your where isn't needed, just an extra last line on the on condition. That extra line can also be written perhaps more clearly like this if you don't like the coalescefunction:

and not (table1.column_a is null and table1.column_b is null and table1.column_c is null);

Or:

and length(table1.column_a||table1.column_b||table1.column_c)>0;