CODEWITHSUNDEEP
sqlpostgresql
San
San

Reputation: 347

How to replace IN clause with JOIN in Postgres?

I have the following query.

select * 
from table table0_ 
where (table0_.col1, table0_.col2, table0_.col3) in (($1, $2, $3) , ($4, $5, $6) , ($7, $8, $9) , ($10, $11, $12) , ($13, $14, $15))

How to replace IN clause with JOIN as shown in the below in the Postgres.

select * 
from table0_ 
where table0_.col1=$1 
  and table0_.col2=$2 
  and table0_.col3=$3

EDIT: I read from somewhere that IN operator does not make use of indexes. Also, this query is taking more time if passing more parameters.

Upvotes: 0

Views: 513

Answers (1)

Pooya
Pooya

Reputation: 3183

I don't know why you should do that because actually no difference between them. You can use the below query and use CTE to create a temp table and join together.

with data as (
  select 
    *
  from (
    values ($1, $2, $3) , ($4, $5, $6) , ($7, $8, $9) , ($10, $11, $12) , ($13, $14, $15)
  ) t (col1, col2, col3)
)
select 
  table0_.*
from 
  table0_, data
where 
  table0_.col1 = data.col1
  and table0_.col2 = data.col2
  and table0_.col3 = $data.col3

Upvotes: 2

Related Questions