 |
 |
Yes, Mysql 5+ supports sub selects. Additionally if you use innodb tables you get transaction support. Duane On Jan 26, 2007, at 11:58 AM, Susan Teague Rector wrote: > 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]