Geek Answers Handbook

MYSQL WHERE-IN Subquery Works Forever

I have a MySQL table. Let me call it Widgets. The Widget table has 3 fields: id , type_id and name . I want, in one request, to get all widgets sharing type_id with widgets called "doodad". I wrote 2 questions:

  • Give me the type_id of the widget named "doodad".
  • Give me all the widgets with this type_name.

It works. Each request independently reaches its goal.

But when I combine them into one subquery, it works forever, an infinite loop style. It looks like this:

SELECT * FROM widgets WHERE type_id IN ( SELECT type_id FROM widgets WHERE name = 'doodad' );

Can anyone explain this? Is it because I'm writing a subquery that works in the same table twice?

Little wheel, why are you cool?

+7
mysql nested
source share
2 answers

There is a problem in MySQL and in , where even uncorrelated subqueries are handled as if they were correlated and reevaluated for each row.

In terms of explanation, the type of choice is likely to appear as a dependant subquery , not just a subquery , as desired.

I suggest that you try using the view that is described at the end of this article to materialize the internal result set.

Or, alternatively, you can see the constify procedure constify

+6
source share

Using JOIN risks duplicates the results - EXISTS will work similarly to IN without the risk of duplication:

 SELECT x.* FROM widgets x WHERE EXISTS (SELECT NULL FROM WIDGETS y WHERE y.name = 'doodah' AND y.type_id = x.type_id)
+3
source share

More articles:


All Articles