Re: [CODE4LIB] MINUS in MySQL (was Re: Thanks!) -- also intesection with LIKE?

[Duane Gran]

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.

have changed but...until recently, you couldn't do sub selects, etc.


/ correlated subqueries, etc.
in Oracle than in MySQL.

Maybe newer versions of MySQL support these more advanced operations?

Jeffrey Barnett wrote:

In general, MySQL seems to have a limited set of SQL operators. That may I've been able to do many more adv SQL oeprations such as set operators

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

have run into.

Ken Irwin wrote:

thousands. There are no such limits on MINUS, or at least none that I

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

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.


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

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:

code, and I wonder if it's possible to use LIKE with the results of a I have script that combs through our logs to weed out spiders, bots and 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

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

anything one way or another.  Is it in one of the later versions of
MySQL?


On Fri, 26 Jan 2007, Jeffrey Barnett wrote:

adding it. I took a quick glance at the docs, but I can't seem to find

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 | [EMAIL PROTECTED]

smime.p7s
Description: S/MIME cryptographic signature