

MySQL, Sub-selects and IN() - thibaut_barrere
http://www.johnewart.net/posts/2011/03/mysql_in_queries/

======
ck2
In plain english, basically you expected mysql to work from inner to outer
parentheses but instead

    
    
       IN (SELECT species_id FROM enemy_species WHERE enemy_id = 123) 
    

is executed against every row in the table instead of just once, taking a
REALLY long time.

I think every everyone goes through that particular mysql learning curve - use
a join instead.

~~~
gaius
Sorry, but that is typical MySQL attitude - when someone finds a bug or a
missing feature, they just say "you shouldn't do it that way".

Same thing they were saying back in the '90s for foreign keys, transactions,
stored procedures...

~~~
ck2
Um, it's not a bug - how would you purposely execute a subquery against every
row in a table?

It's doing exactly what you told it to do.

The bug is a human bug of not understanding what you are asking even though it
seems logical.

~~~
gaius
If it's impossible then why do both Oracle and SQL Server handle this scenario
correctly? I've just tried it on both.

(I do know the answer - it's because they can do hash joins and MySQL can't).

------
zzzeek
If you're looking to actually think relationally, that is, composing more
complex queries from simpler components, as opposed to using only find-row-by-
id types of queries (the kind that work equally well with a noSQL store like
mongo anyway), you'd best use Postgresql, or if you like to spend money for no
good reason an Oracle or SQL Server.

~~~
thibaut_barrere
What makes you tell money spent on SQL Server is spent for no good reason ?
(real question)

Afaik a lot of .Net shops I know are fairly happy with it, to say the least.

~~~
chrisjsmith
I don't think any .net shops are particularly happy with it - they know of no
other tool.

My personal opinion: There are far less mentally and emotionally demanding
things that do as good a job for zero outlay and don't suddenly steal your
wallet whenever you try and scale them up.

Having said that, I've used it since SQL 6.5 and it supports small to medium
workloads fine. It's also significantly less ugly and needy than Oracle.

I'd pick PostgreSQL if I was going solo on a project though.

~~~
thibaut_barrere
The .Net shops I know are aware of other tools (including MySQL, Redis,
MongoDB...), so I guess we're not talking about the same shops.

Thanks for the rest of your feedback though :)

~~~
chrisjsmith
The ".net shop" I work for uses MySQL, MongoDB as well. But it's a rarety
having done a lot of contract work over the years.

------
troels
Oh yes, MySql is awkward. You get used to some of those subtleties after a
while, but it would be nice if you didn't have to.

~~~
forkandwait
May I suggest using Postgres?

~~~
troels
You know. After a while you just get used to the quirks. Postgres has its own
oddities. Granted they have a tendency to be by design, rather than glaring
errors, but can still be annoying.

------
Hawramani
I wonder if NOT NOT IN would have solved it since he mentions the problem
doesn't exist for NOT IN.

~~~
jswinghammer
No. MySQL is able to optimize an anti-join but not the IN condition. It works
about the same as having a LEFT JOIN with a not is null check in the where
clause. MySQL will see NOT NOT IN as just IN.

~~~
Hawramani
Would using parentheses (NOT(NOT IN...)) make a difference?

~~~
jswinghammer
Using NOT NOT IN is a syntax error in any event. I was answering in the
hypothetical in any event.

~~~
Hawramani
Ah I see. Thanks for the answer.

------
tedjdziuba
FWIW this is why the rest of us chuckle at MySQL.

