CODEWITHSUNDEEP
sqlpostgresqllistjoin
Yanirmr
Yanirmr

Reputation: 1032

How do I convert a list of arbitrary values into a subquery that can be used in a join

I want to join a list of values with table rows in SQL.

Shortly, I have a list of elements and I want to join this element with a given table.

For example, my list is (1,3,5,100,200,700) and the table is:

id | val
 1 |  a
 2 |  b
 3 |  c
 4 |  d
100|  e
200|  f

I know how to do that with in clause:

 SELECT * FROM table 
 WHERE id IN(list)

Unfortunately, in my situation (very-very long list) I cannot use in clause and required using join with that list.

How to convert list (in the format that "in" can deal with) to something that can be joined.

Important constraint: I don't have writing permissions in the database so answers like "write this list into new table and then join them" - don't help me. Generally, I need to use this practice hundreds times in the code, so create a new table for every query not feasible even I have writing permissions.

Can you help me, please?

Upvotes: 0

Views: 332

Answers (1)

user330315
user330315

Reputation:

If that list is a string, you can e.g. replace the parentheses with curly braces and cast it to an array:

where id = any('{1,3,5,100,200,700}'::int[])

Or if you can't change the input:

where id = any(translate('(1,3,5,100,200,700)', '()', '{}')::int[])

The same approach can be used for joining: 

select mt.*
from my_table mt
  join unnest('{1,3,5,100,200,700}'::int[]) as x(id) on x.id = mt.id

But those two solutions aren't the same: if the list contains duplicates the join will return duplicate rows as well. The id = any() condition will not do that.

Upvotes: 3

Related Questions