 |
 |
I must admit I've never used MySQL, but the set operations are part of the SQL92 standard. I've used them in both Oracle and DB/2, and I think Access, but I'm not sure. I often work with *very* large result sets (millions), and the "IN SELECT()" notation has limits in the few thousands. There are no such limits on MINUS, or at least none that I have run into. Ken Irwin wrote: > The MySQL online documentation doesn't include a MINUS command, just a > bunch of reference to minus signs (-). > Thankfully, there's more than one way to do this! > > Also: having these handy new tools, I'm revisiting some old, cludgy > code, and I wonder if it's possible to use LIKE with the results of a > subquery, eg.: > SELECT * FROM table WHERE ip [NOT LIKE ANYTHING IN] (SELECT ip_range > FROM known_ips) > where [NOT LIKE ANYTHING IN] is probably some different wording. > > I have script that combs through our logs to weed out spiders, bots and > whatnot, and it references a table full of known good IPs that are > definitely real users. Right now I have this hideous long query that > includes a "WHERE ip not like '136.227.%' and ip not like '123.345.%' > and...". If there's a way to similarly slim down this statement, I would > love to find it. > > I have a feeling the SQL has much vaster powers than I know how to harness! > > thanks, > Ken > > Jonathan Gorman wrote: >> Last I checked MySQL doesn't support MINUS, but it's been a few years >> since I used it. I vaguly remember talk about the developers planning on >> adding it. I took a quick glance at the docs, but I can't seem to find >> anything one way or another. Is it in one of the later versions of >> MySQL? >> >> >> On Fri, 26 Jan 2007, Jeffrey Barnett wrote: >> >>> You have gotten a lot of suggestions, but here is one more. >>> >>> select * from lib_books where good_thing = 'TRUE' >>> MINUS >>> select * from lib_books where bad_thing = 'TRUE' >>> >>> I think MINUS is faster than JOIN. >>> >>> Other SET OPERATIONS include UNION and INTERSECT. >>> >>> Set operations require that the underlying result sets be "compatible": >>> Same number of columns. >>> Corresponding columns have matching datatypes. >>> >>> >>> Ken Irwin wrote: >>>> Hi all, >>>> >>>> Thanks for these myriad responses! I've gotten at least three distinct >>>> approaches to try. I knew there had to be a better way. >>>> >>>> your sql-fu is appreciated! >>>> >>>> joys >>>> Ken >>> > > -- > Ken Irwin > Reference Librarian > Thomas Library, Wittenberg University