List:General Discussion« Previous MessageNext Message »
From:Tod Harter Date:June 17 2002 4:02pm
Subject:Re: queries within queries
View as plain text  
On Monday 17 June 2002 13:00, Bhavin Vyas wrote:

The one thing that you CAN'T duplicate with a JOIN is some types of 
correlated sub-queries. For instance most SQL engines would allow something 
like:

delete from X where NOT EXISTS (SELECT y.a WHERE y.a=x.a)

which would effectively be like a cascading delete into Y from X where the 
relation in each table is on column A. Standard SQL would execute the 
subquery where x.a would assume a value for each record in X. In the case of 
data management operations like drop you can't simulate this with a join. I 
expect this is the main motive for adding subqueries. Your alternative in 
this case is to use InnoDB tables and create a relation on y.a to x.a. I 
don't recall off the top of my head if InnoDB supports cascading delete yet, 
but that would be what you would use. Other than that you should be able to 
transform any subquery into an equivalent join, though it can get pretty 
ugly...

And no, subqueries are not supported AFAIK in any version of MySQL at this 
time. If you can't live without some of these features you may just have to 
use Postgres or Firebird or a commercial database...
 
> Nested queries aren't supported in 3.23.xx (not sure about 4.x). However,
> most of the things that you can do with nested quries are possible with
> JOIN(which should make what you are looking for, possible), so you might
> want to look into that.
>
> Regards,
> Bhavin.
> ----- Original Message -----
> From: "W. Enserink" <enserink@stripped>
> To: <mysql@stripped>
> Sent: Monday, June 17, 2002 5:33 AM
> Subject: queries within queries
>
> > hi all,
> >
> >
> > I made a query: the result are given back to me. Now I want to perform
> > another query within these results.
> >
> > Is this possible?
> >
> >
> > thx for all tips.
> >
> > regards Wilbert
> >
> > -------------------------
> > Pas de Deux
> > Van Mierisstraat 25
> > 2526 NM Den Haag
> > tel 070 4450855
> > fax 070 4450852
> > http://www.pdd.nl
> > info@stripped
> > -------------------------
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> >    http://www.mysql.com/manual.php   (the manual)
> >    http://lists.mysql.com/           (the list archive)
> >
> > To request this thread, e-mail <mysql-thread112182@stripped>
> > To unsubscribe, e-mail
>
> <mysql-unsubscribe-bvyas3=comcast.net@stripped>
>
> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
>
> To request this thread, e-mail <mysql-thread112193@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-tharter=aptusventures.com@stripped> Trouble
> unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Thread
Working libmysqld.a for Tru64 5.1?Randall Severy16 Jun
  • Re: Working libmysqld.a for Tru64 5.1?Dan Nelson16 Jun
  • queries within queriesW. Enserink17 Jun
    • RE: queries within queriesNick Arnett17 Jun
  • Re: queries within queriesBhavin Vyas17 Jun
    • Re: queries within queriesTod Harter17 Jun
  • temporary tableWilbert Enserink18 Jun
    • Re: temporary tableVictoria Reznichenko18 Jun
  • Re: temporary tableSammy Lau18 Jun
RE: queries within queriesRobert Casey17 Jun
RE: temporary tableHendrik Schalekamp18 Jun