CODEWITHSUNDEEP
sqlpostgresql
krishna sai
krishna sai

Reputation: 185

How to convert an IN clause having actual values into a join clause?

I would like to convert an IN clause which consists of actual values and not a subquery using join clause.

select max(c1) from t1 where t1.id IN (
1,2,3,4......1000
)
and t1.c2 = something

How do I convert this query into an join clause?

Upvotes: 1

Views: 728

Answers (3)

Radim Bača
Radim Bača

Reputation: 10701

Use generate_series if your sequence is increasing linearly

select max(c1) from t1    
join generate_series(1, 1000) ids on t1.id = ids
where t1.c2 = something

Upvotes: 2

MatBailie
MatBailie

Reputation: 86715

You can build a data-set by using an inline VALUES, and use aliasing to give that data-set and name and column names.

SELECT
    yourData.id, map.b
FROM
    yourData
INNER JOIN
(
  VALUES
    (1,2),
    (2,4),
    (3,8)
)
  map(a,b)
    ON map.a = yourData.a

Upvotes: 3

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can try like below

 with cte as
    (
    select 1 as col union all
    select 2 union all
    select 3 union all
    select 4 union all
    ---------
    -------
    select 1000
    ) select max(t1.c1) from 
       cte join t1 on cte.col=t1.id where t1.c2=something

Upvotes: 0

Related Questions