Matt, I humbly beg to differ. I believe that what you wrote will be executed as a correlated subquery. That would make your statement much slower than a JOIN (especially if the correct indexes exist).
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. Of course, I could be very wrong and I will have learned something very valuable today. With greatest respect, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matt Warden <[EMAIL PROTECTED]> wrote on 08/06/2004 02:03:56 PM: > On Fri, 06 Aug 2004 13:17:42 -0400, Michael Stassen > <[EMAIL PROTECTED]> wrote: > > You need a LEFT JOIN: > > > > SELECT ticket_number > > FROM purchased_items LEFT JOIN purchases > > ON purchased_items.ticket_number = purchases.ticket_number > > WHERE purchases.ticket_number IS NULL; > > > No, actually he doesn't. > > SELECT ticket_number > FROM purchased_items > WHERE ticket_number NOT IN > (SELECT ticket_number FROM purchases); > > The above will most certainly be faster than any join, because it is > only a couple projections and a simple selection over ticket_number > (which is almost certainly indexed). > > Although, I suppose if this is only a maintenance query (I suspect it > is), then it probably doesn't matter. But, the bottom line is: if you > can avoid join, do it. There's only so much the query optimizer can > do. > > > > > -- > > Matt Warden > Berry Neuroscience Lab > Department of Psychology > Miami University > > > > 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] >
