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
|