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))
[download]

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=?;
[download]

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).

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.