Is it possible to use IN operator with SubQuery

select * from emp as e where e.code in (select codes from codetable where xx =10);
i am not getting results for this query bur emp code is available in codetable

You need to get the subquery to produce a plain array of codes. That requires using UNNEST to get one code per row, and then RAW to strip away the wrapper object in the result from the subquery.

Assuming codetable.codes is an array of integers and emp.code is an integer, this works:

select * from emp as e where e.code in (select raw code from codetable unnest codetable.codes as code where codetable.xx = 10)