MySQL IN query support is lacking

I’ve been working with MySQL lately and was really surprised that it can’t do simple IN queries using a table as the IN recordset. One of these that I tried was later identified by the very system we’re writing as being a long running query (it had been running for over a day.) Fortunately you can write them using an inline table instead.

Doesn’t work:

select stuff from OutsideTable where id in (select id from InsideTable);

Works:

 select ot.stuff 
 from OutsideTable ot,
      (select id from InsideTable where something='value') it
 where ot.id=it.id;

Well, at least I could make it work.

(Oh, I didn’t run these queries, they’re just there to make the point).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s