Referencing outer query in subquery

Question

I have the following query, which generally works, and is supposed to return all rows covering the define timeframe (taking the closest prior and next rows if no absolut match - outlined at http://www.orafaq.com/node/1834)

SELECT * FROM table
  WHERE id=__ID__ AND `date` BETWEEN 
    IFNULL((SELECT MAX(`date`) FROM table WHERE id=__ID__ AND `date`<=__LOWERLIMIT__), 0)
  AND
    IFNULL((SELECT MIN(`date`) FROM table WHERE id=__ID__ AND `date`>=__UPPERLIMIT__), UNIX_TIMESTAMP())
ORDER BY `date`

but was hoping to reduce the two table subselects by referencing to the outer select, but obviously it doesnt like it

SELECT * FROM (SELECT * FROM table WHERE id=__ID__) b
  WHERE `date` BETWEEN 
    IFNULL((SELECT MAX(`date`) FROM b WHERE `date`<=__LOWERLIMIT__), 0)
  AND
    IFNULL((SELECT MIN(`date`) FROM b WHERE `date`>=__UPPERLIMIT__), UNIX_TIMESTAMP())
ORDER BY `date`

Is there a way to have the query without the three table selects?

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

You can do something like this with a join:

select * from table a
    inner join (
       select id,
              max(
                  if(`date` <= __LOWERLIMIT__ ,`date`, 0)
              ) as min_date,              
              min(
                 if(`date` >= __UPPERLIMIT__ , `date`, UNIX_TIMESTAMP())
              ) as max_date
           from table
           where id = __ID__
           group by id
    ) range on
    range.id = a.id and
    a.`date` between min_date and max_date;

I'm not a MySQL expert, so apologies if a bit of syntax tweaking is needed.

Update: the OP also found this very nice solution.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 21 '12 at 09:24

dan1111

6,576
2
18
29

Thank you Dan. Primarily I am concerned about the performance with the three selects (the table will get rather big, otherwise it wouldnt be that much of an issue) but the present example also involves an additional joint subselect and without having actually measured it I'd be worried it might have similar potential performance issues. Did you have a particular reason to suggest it over the initial query? Thanks a million – Nov 21 '12 at 09:33
@user1841321, I believe that if you had 1000 rows for a given ID, in your original the subqueries for max and min would be repeated 1000 times. With this join solution, the calculation of max and min only happens once. – dan1111 Nov 21 '12 at 09:40
Do you believe it would be called 1000 times because of the outer query? Wouldnt it do the same here (the second select being a subselect of the join)? Also RANGE ON appears not be mysql specific, what would it purpose be? – Nov 21 '12 at 10:51
@user1841321, "range" is not a command; it is just the name I have given to the subselect that I am joining--similar to the first table being referred to as "a". – dan1111 Nov 21 '12 at 11:11
A subquery in a join will always be processed once, before the join takes place, while a subquery in a `WHERE` clause is processed for each row. The reason is that the subquery in a `WHERE` clause could depend on something specific to the particular row being considered. – dan1111 Nov 21 '12 at 11:13
Sorry, I read it as RANGE ON and wondered why I couldnt find anything about it and deducted it would be a something either Oracle or SQL Server specific. Thanks for the clarification. – Nov 21 '12 at 11:23
Only the cases in MIN and MAX in the subselect throw a syntax error. What would the case refer to? – Nov 21 '12 at 12:43
@user1841321, perhaps your version of MySQL doesn't support `CASE`. I changed it to `IF()`--does that help? – dan1111 Nov 21 '12 at 13:11
IF appears to work even though CASE should have worked as well, I am on 5.5. I am wondering though which "date" values the ifs a querying. – Nov 21 '12 at 13:18
@user1841321, each `date` values are all of the dates in the inner query (i.e. all dates for `__ID__`). Then `max()` and `min()` are applied to the combined results of all `if()` statements. – dan1111 Nov 21 '12 at 13:31
At times the most obvious answer is sort of hidden :) http://stackoverflow.com/a/5914974/1841321 even though it is three unioned selects I would still assume it is the least expensive way. – Nov 22 '12 at 17:25
@user1841321, I agree, that is a nice answer. – dan1111 Nov 23 '12 at 08:59

Referencing outer query in subquery

1 Answers1

Linked