6

I've got to execute a self-joining sql statement on a derived table in mysql. The derived table involves a hairy subquery, and I'm wondering if there's any alternative to actually writing and executing it twice- 

SELECT a.* FROM (my hairy subquery) AS a
  LEFT JOIN (my hairy subquery) AS a2 
    ON a.groupname = a2.groupname etc..
Yarin
  • 173,523
  • 149
  • 402
  • 512
  • That depends - we're going to need to see the entire query to be able to answer this. – Clockwork-Muse Aug 24 '12 at 22:11
  • 2
    X-Zero- no you don't- this isn't a query specific question – Yarin Aug 24 '12 at 22:13
  • 1
    @Yarin: Posting the full query would be useful because there may be a simple workaround for your specific query (e.g. finding a way to rewrite the query without using a self join) that doesn't require answering the more general question. – Mark Byers Aug 24 '12 at 22:14
  • Again, its not about the specific query- if you're interested, it stems from an involved [question](http://stackoverflow.com/a/12114175/165673) we already have extensive answers to – Yarin Aug 24 '12 at 22:17
  • @Yarin: There are much better ways to do that which have been covered many, many times before on StackOverflow that a) don't require a self-join and b) run much faster. I suggest you search a bit more and find a better approach instead of pursuing this dead-end. – Mark Byers Aug 24 '12 at 22:27
  • 1
    Mark- I'd be really interested to know which better solutions you're talking about- the solution is question derives from [Bill Karwin's](http://stackoverflow.com/a/1442867/165673), who I tend to trust, and that was after we spent the better part of the day searching SO. If you've got something better, please jump in on the question and offer an answer. – Yarin Aug 24 '12 at 22:35
  • @Yarin: I was going to post an answer then I noticed that the correct approach has already been posted **in the answer that you accepted**!!! Why aren't you using that approach?! – Mark Byers Aug 24 '12 at 22:59
  • I posted my answer. I rarely answer questions more than one hour old that already have answers, because it seems that no-one bothers to read the late answers, not even the OP. Let's hope that this time is the exception... – Mark Byers Aug 24 '12 at 23:22
  • 1
    Mark I'm reading your post- thanks for that. Honestly the reason I wasn't using my accepted answer was because I was having trouble applying it to my subquery- but I think a temp table might help me with that. I'm going to try again using your answer.. – Yarin Aug 25 '12 at 00:40
  • Well now I'm confused, because the only answer I see (which is also accepted) is the one from @MarkByers, what happened to the previously accepted answer? – Michael Oct 31 '17 at 20:52

1 Answers1

4

The standard solution to this is to use CTEs, but these are not yet supported in MySQL. Alternatives are:

  • You can put your subquery in a view and self-join the view.
  • You can create a temporary table and populate it with the results of your subquery.

Related

Community
  • 1
  • 1
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Is putting the subquery in a view more performant than executing it twice? – Yarin Aug 24 '12 at 22:19
  • 2
    @Yarin - Barring any query-specific optimizations, probably not, as you're (likely) still executing the view twice as well (don't work in mySQL, sorry). If the 'subquery' takes a significant portion of time, then moving it to a temp table probably will (note that in those systems that do support CTEs, they can do this under the covers, if you reference it more than once). – Clockwork-Muse Aug 24 '12 at 22:38
  • X-Zero- thanks, that's what I'm seeing, views basically suck in MySQL. I'll try temp tables, seems the only decent option – Yarin Aug 24 '12 at 22:50