tag:blogger.com,1999:blog-15319370.post5692473004958743960..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: Refactoring: Derived table, UNION...WTF?rpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-15319370.post-51146074599194906382006-11-23T20:20:00.000+01:002006-11-23T20:20:00.000+01:00Hi Andrew (gilf! long time no see, how are you mat...Hi Andrew (gilf! long time no see, how are you mate?!)<br /><br />I think a CASE expression can always be translated into an IF expression, and vice versa. The example from the query:<br /><br />CASE rating<br /> WHEN 'PG-13' THEN 'PG'<br /> WHEN 'NC-17' THEN 'R'<br /> ELSE rating<br />END <br /><br />is a so-called "simple case expression". It's called simple because there are no compound expressions involved in evaluating the WHEN alternatives. This is a lot like the Oracle DECODE() function (The MySQL DECODE() function is an entirely different thing!).<br /><br />Anyway, let's translate this to a "searched case expression", just for fun:<br /><br />CASE <br /> WHEN rating='PG-13' THEN 'PG'<br /> WHEN rating='NC-17' THEN 'R'<br /> WHEN rating<br />END <br /><br />As you can see, this is not a "simple" evaluation anymore. Each when alternative tests an entire condition. We just happen to test against the RATING column for each WHEN, but we don't have to. We can test entirely unrelated conditions with the searched case, and even use compound conditions with AND and OR.<br /><br />I am doing this translation to show how much this is like the classic IF...THEN...ELSEIF...ELSE logic that everybody is familiar with:<br /><br />IF rating='PG-13' THEN 'PG'<br />ELSEIF rating='NC-17' THEN 'R'<br />ELSE rating<br />END<br /><br />Well, only one trivial step is to separate the ELSIF into ELSE and IF:<br /><br />IF rating='PG-13' THEN 'PG'<br />ELSE<br /> IF rating='NC-17' THEN 'R'<br /> ELSE rating<br />END<br /><br />And this is of course already almost like the MySQL IF() function you are referring to:<br /><br />IF(<br /> rating='PG-13'<br />, 'PG'<br />, IF(<br /> rating='NC-17'<br /> , 'R'<br /> , rating<br /> )<br />)<br /><br />So yeah. I can't prove it formally, but I am pretty sure any CASE construct can be translatd into a series of nested IF..ELSE constructs.<br /><br />(Note: in the functions section of the MySQL Manual, these CASE and IF expressions are called "Control Flow Functions", see http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html I don't like that name at all, because to me, these are plain expressions, that is, operations that yield a value. "Flow of Control" is something I associate with a process - kind of what is described as "Flow Control Contructs" here http://dev.mysql.com/doc/refman/5.0/en/flow-control-constructs.html So I stubbornly refer to these functions as "Conditional Functions". The SQL Standard refers to all these things as "Case Expressions") <br /><br />Your note on the soccer team example is true too. Oracle has analytical functions that will allow you to do this. Interestingly, the analytical functions are implemented by saving intermediate resultsets in temporary tables and requerying those to get the aggregate-of-and-aggregate effect. Of course, a subquery is also just a temptable - at least, in practice it is. It's probably more correct to say that a subquery is a recipe to create a temptable, and that by writing down the recipe, you automagically create such a temporary table.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-66545612967769620922006-11-23T18:06:00.000+01:002006-11-23T18:06:00.000+01:00Interesting post Roland, could you have used an IF...Interesting post Roland, could you have used an IF instead of a CASE? Not sure what effect that would have on performance. <br /><br />Often with SQL you stuff that works but isn't exactly optimised, due in part to many developers lack of experience with the more detailed level of SQL syntax. <br /><br />With regards to your soccer team example I think in Oracle you could use Analytical Queries to do that.Andrew Gilfrinhttps://www.blogger.com/profile/09929358844206555905noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-52197691477347350682006-10-18T13:22:00.000+02:002006-10-18T13:22:00.000+02:00By the way, Toasty:
"Also I'd have thought that t...By the way, Toasty:<br /><br />"Also I'd have thought that the optimiser would smart enough to rewrite this part but it doesn't seem to be..."<br /><br />I'm pretty sure the MySQL optimizer does not optimize the UNION here. I'd have to check and see what Oracle does.<br /><br />I really did not get a chance to look thorough enough to the real system, so it's all conjecture.<br /><br />However, I thought it would be nice to take a refactoring example that focusses on only logic thinking, instead of the more 'hardcore' performance tuning activities which usually dominate in query rewrite articles.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-33473387814858700832006-10-18T13:15:00.000+02:002006-10-18T13:15:00.000+02:00Toasty:
yes, you are right, good spot! Thanks fo...Toasty: <br /><br />yes, you are right, good spot! Thanks for mentioning it.<br /><br />However, I chose these :) In the original query, we were dealing with real DATE fields, and I adapted it to the sakila database. Incidentally, all the films there have the release_year 2006, but i should of course've taken a more convincing period. <br /><br />How ironic that I should make that mistake in an article like this. May it remain here on the web forever as a testimony to my own remarks regarding that.<br /><br />Roland Boumanrpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-34472669674832936012006-10-18T11:17:00.000+02:002006-10-18T11:17:00.000+02:00Good article, great original query
;)
BTW, isn't...Good article, great original query <br />;)<br /><br />BTW, isn't:<br /><i><br />where release_year > 2005<br />and release_year <= 2006</i><br /><br />the same as:<br /><i>where release_year = 2006</i><br /><br />Or do we have fractional year parts? :)<br /><br />Also I'd have thought that the optimiser would smart enough to rewrite this part but it doesn't seem to be...Anonymousnoreply@blogger.com