sqlsqlitecorrelated-subquery

How can I avoid duplicating multi-column correlated subqueries?


I want a correlated sub query that returns multiple columns and filters itself based on values in the main table.

A list of student test scores for a set of days:

create table student
(id, name     ); insert into student values
(1 , 'joe'    ),
(2 , 'steve'  );

create table testScore
(id, studentId, day  , score); insert into testScore values
(1 , 1        , 5    , 70   ),
(2 , 1        , 10   , 68   ),
(3 , 1        , 15   , 95   ),
(4 , 1        , 20   , 81   ),
(5 , 2        , 5    , 100  ),
(6 , 2        , 10   , 75   ),
(7 , 2        , 15   , 98   ),
(8 , 2        , 20   , 92   );
create index testScore_studentTopScores on testScore (studentId, score, day);

If I need to get the top test score for each student (and include the corresponding day numbers, and have the testScore_studentTopScores index be used) I would love to do:

select
  student.name,
  topScore.score as topScore,
  topScore.day as topScoreDay
from
  student
  left join (
    select * from
      testScore
    where
      testScore.studentId = student.id
    order by
      score desc
    limit 1
  ) as topScore
order by
  topScore desc;

And have it return:

name topScore topScoreDay
steve 100 5
joe 95 15

But from clause subqueries can't reference outer/parent tables. Column subqueries and where clause subqueries are able to access tables in the from clause, but cannot return multiple columns. So I have to duplicate the subquery for every column:

select
  student.name,

  (
    select
      score
    from
      testScore
    where
      testScore.studentId = student.id
    order by
      score desc
    limit 1
  )  as topScore,

  (select day from (
    select
      score,
      day
    from
      testScore
    where
      testScore.studentId = student.id
    order by
      score desc
    limit 1
  ))  as topScoreDay

from
  student
order by
  topScore desc;

Neither SQLite nor MySQL can do this without duplication for each column. Microsoft SQL Server has an outer apply join operator; like a left join but where the clause can reference prior tables in the from clause. That's what I want in SQLite.

Also, the solution should not be an SQL trick to just handle this simple example. It needs to be general enough to be scalable for any large and complex query.


Solution

  • According to this answer from Database Administrators SQLite only disallows access to outer tables when directly joining subqueries. It does allow access to outer tables inside subqueries that are part of on clauses.

    I'm using a "CORRELATED SCALAR SUBQUERY" according to EXPLAIN QUERY PLAN, which allows access to the current row of the parent student table. No need to use limit 1 in the subquery because SQLite returns only the first row to satisfy the on clause.

    select
      student.name,
      testScore.score as topScore,
      testScore.day as topScoreDay
    from
      student
      left join testScore on testScore.id = (
        select id from
          testScore
        where
          testScore.studentId = student.id
        order by
          score desc
      )
    order by
      topScore desc;