Wednesday, December 21, 2016

Creating a dynamic table out of list of numbers to join

I had a list of ids and I wanted to check which rows are not present in the DB.

We can use 'in (1, 2, 3)' and that would say which ones are in, and 'not in' would return rows outside the ids I had.

So I wanted to make a table out of the ids and use left or right join.

I could not find any simple way of doing this and then the approach mentioned here

select * from (
select TO_NUMBER(xt.column_value) id1 from
123459') xt) a
left join my_table mt on = a.id1
where is null

Indeed I had 1000 of Ids in my list and not the above 4 :)

I choose left join as my_table had thousands of rows.

I really appreciate effort of anyone to answer on SO.

No comments: