Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

Try IN with sub-select instead of placeholders

by blssu (Pilgrim)
on Oct 02, 2002 at 14:03 UTC ( #202281=note: print w/replies, xml ) Need Help??

in reply to Re: DBI question with placeholders and IN
in thread DBI question with placeholders and IN

If you were using Oracle, then the code you wanted to run should have worked as-is. You could also use UNION in a sub-select like this:

SELECT events.b_g FROM events WHERE events.b_g NOT IN ((SELECT otf.b_g FROM otf) UNION (SELECT requests.b_g FROM requests))
That shouldn't be any faster, but the logic is cleaner IMHO.

I agree with you that there are alternatives to using IN with literals (or placeholders). All of those alternatives require the values you want to test to be in the database though.

If you ever find code using lots of similar IN queries, sometimes it's worth creating a temporary table to cache values for your IN clauses. It works like this:

# insert all of the values you want to test with IN INSERT INTO temp (id, value) VALUES (?, ?); # now replace the placeholder IN with a sub-select SELECT ... WHERE column IN (SELECT value FROM temp WHERE id=?); # after you're completely done, remove the values -- you # might just rollback instead of deleting if the inserts # were never commited. DELETE FROM temp where id=?;
I use code very similar to this when a user logs into our application. Lots of multi-valued security and preference checks can be flattened out into a temporary table. The IN statements become much simpler and more robust (you don't have to worry about the query blowing up if you have too many placeholders).

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://202281]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (5)
As of 2021-10-26 08:02 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (90 votes). Check out past polls.