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.
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;