> I believe that when the query engine executes your statement, for each
> row of purchased_items data it looks at, it will have to run the query
> "SELECT ticket_number FROM purchases" scan those results and
> determine if the current row matches. If it is in the list then it wil exclude
> that row from the final results.
I would find it very silly if mysql's query optimizer decided that the
optimized way to execute the query is to execute "SELECT ticket_number
FROM purchases" N times, where N is the number of rows in
purchased_items. There is no reason why that query would be executed
any more than one time.
FWIW, there is a correlated subquery version of this query (the
example I gave is uncorrelated), and it would be the following:
SELECT ticket_number
FROM purchased_items a
WHERE NOT EXISTS
(SELECT * FROM purchases b WHERE a.ticket_number=b.ticket_number);
I cannot say for certain that these two queries are not executed by
mysql in the same manner, but I would be surprised if they were. I say
this because you could alter my query and use an explicit set:
SELECT ticket_number
FROM purchased_items
WHERE ticket_number NOT IN
(112, 456, 942, 356, 623, 783);
I would find it more likely that the above query is executed in the
same way as my original solution.
And, Emmet Bishop insightfully commented:
> You're making the assumption that he's using 4.1.x. He
> didn't state which version he's using so your solution
> may be of no use to him.
Good point. I often forget about which features are/were unimplemented
in mysql. My apologies.
--
Matt Warden
Berry Neuroscience Lab
Miami University
http://mattwarden.com
This email proudly and graciously contributes to entropy.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
