Databases
StackOverflow2013
Postsdbo
POSQL SELECT statement expression value reuse for other expression
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL SELECT statement expression value reuse for other expression
    primarykey
    data
    text
    <p>I have a table with huge row count in mysql (though I am looking for generic SQL solution)</p> <pre><code>very_big_table(INT a, INT b, INT c, ...) </code></pre> <p>I wanted SELECT statement</p> <pre><code>SELECT a, (b + c) as expression1, (b + c + a) AS expression2 -- basically (expression1 + a) FROM very_big_table WHERE ... GROUP BY a ORDER BY a DESC </code></pre> <p>this looks good and easily readable as long as the expression1 is simple.<br> But when CASE-WHEN/IFNULL()/SUM()/MIN()/STRCAT() or some Operator comes into play in these expressions its difficult to read and debug. </p> <p>I have gone through some of the already asked questions<br> <a href="https://stackoverflow.com/questions/5623410/assigning-mysql-value-to-variable-inline">assigning mysql value to variable inline</a><br> <a href="https://stackoverflow.com/questions/5180979/use-value-of-a-column-for-another-column-sql-server">Use value of a column for another column (SQL Server)?</a><br> <a href="https://stackoverflow.com/questions/5790697/how-to-use-conditional-columns-values-in-the-same-select-statement">How to use conditional columns values in the same select statement?</a> </p> <p>But if I use the approaches described something like</p> <pre><code>SELECT a, expression1, (expression1 + a) AS expression2 FROM (SELECT a, (b + c) AS expression1 FROM very_big_table WHERE ... GROUP BY a) as inner_table ORDER BY a DESC </code></pre> <p>this works fine, but this query is taking some 70x more time to execute. Atleast when i fired it, though only once.<br> what if I have multiple levels of the <em>expressions</em> in the output columns? </p> <p>Is there any elegant way to deal with this, without compromising readability?</p> <p>BTW why isnt this <em>expression reuse</em> or <em>alias reference</em> in select statement not supported by SQL standards or vendors? (supposing there are no cyclic evaluation in the single SELECT statement expressions. in that case the compiler fails)</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USuser918953
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL SELECT statement expression value reuse for other expression
      1. USDaniel Hermosel
      1. VTFavorite
    2. VO
      singulars
      1. POSQL SELECT statement expression value reuse for other expression
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL SELECT statement expression value reuse for other expression
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy do you think that being able to reuse the expression alias will lead to a 70x reduction in execution time? Can you show a real example with real queries and the .sqlplan files for an actual execution plan? I can almost guarantee that such a disparity in execution time is not due to adding a subquery - something else must be different.
      singulars
      1. POSQL SELECT statement expression value reuse for other expression
      1. USAaron Bertrand
    2. CO@Quail still can't envision a scenario where that's the only difference and it leads to a 70x performance difference.
      singulars
      1. POSQL SELECT statement expression value reuse for other expression
      1. USAaron Bertrand
    3. COI deleted my comment after Aaron edited his. For posterity it said, "I imagine he's comparing it specifying every column explicity."
      singulars
      1. POSQL SELECT statement expression value reuse for other expression
      1. USmkjeldsen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload