 |
 |
I'm not surprised that MySQL doesn't support set operations. In general, MySQL seems to have a limited set of SQL operators. That may have changed but...until recently, you couldn't do sub selects, etc. I've been able to do many more adv SQL oeprations such as set operators / correlated subqueries, etc. in Oracle than in MySQL. Maybe newer versions of MySQL support these more advanced operations? Jeffrey Barnett wrote: > 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 -- Susan Teague Rector Web Applications Manager VCU Libraries: Library Information Systems 804.827.3554 | [log in to unmask]