[DB-SIG] Parameter substitution and "IN" operator

Gerhard Häring gh at ghaering.de
Tue Mar 18 01:18:59 CET 2008

Hash: SHA1

Alex Willmer wrote:
> On Tue, 2008-03-18 at 00:14 +0100, Gerhard Häring wrote:
>> This is not the way SQL parameter binding works. SQL parameter binding
>> only works with scalars (*). If tuples seem to work, then that's just
>> a side-effect of the implementation of your particular DB-API module.
>> >From here, it seems like native SQL parameter binding for tuples is a
> good thing. Do relational databases not implement it merely because no
> one has written the code yet, or is it missing from the SQL standard/a
> bad idea in principle?

It's missing from the SQL standard. And it's not implemented because
it would make the main idea of parameter binding useless: reusing
query plans.

I agree that in theory the "SQL-to-virtual-machine compiler" on the
database backends could be smarter like this, but it would probably
add too much complexity.

If you really really need the functionality with "IN" and want native
parameter binding, you can fake it using temporary tables.


<fill table tmp_table>
select ... from table1 where col1 in (select col2 from tmp_table).


<fill table tmp_table>
select ... from table1 where exists (select 1 from tmp_table where

The EXISTS version is better with some databases (certainly it was for
Oracle 8, which just "cut off" values in "IN" clauses if you had more
than 255 or something).

- -- Gerhard
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org


More information about the DB-SIG mailing list