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 table table0_
    where table0_.col1=$1
        and table0_.col2=$2
        and table0_.col3=$3
    


  • I assume you mean:

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

    Which would correspond with:

    select * 
    from table0_ 
    join ( values ($1, $2, $3) 
                , ($4, $5, $6) 
                , ($7, $8, $9) 
                , ($10, $11, $12) 
                , ($13, $14, $15) ) t (col1, col2, col3)
        using (col1, col2, col3);
    

    If the derived table is large you may be better off using a temporary table and inserting your parameters there

    CREATE TEMP TABLE t 
    ( col1 ...
    , col2 ...
    , col3 ... );
    

    INSERT INTO T (col1, col2, col3) VALUES ($1, $2, $3), ...

    select *
    from table0_
    join t
    using (col1, col2, col3);




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2