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]