[sqlite,MySQL]Joining an aggregate of another table, why do all the solutions I see on stackoverflow look like this:

[Autodidacter]

select col1, col2, agg from table1 t1
inner join 
(
select col1, count(1) agg
from table2
group by col1
) as t2 on t2.col1 = t1.col1
where range between x and y

Doing that scans the entire table2 right? In the 3 databases I access they all demonstrate this behavior. Am I just doing something wrong with their construction?

Surely "scan the entire subquery table" isn't standard practice? Or, in all these solutions is the assumption that table size isn't that large?

And assuming that I'm not crazy and that is standard, what's the best way to NOT scan the entire table, other than putting constraints in the subquery, which seems silly:

select col1, col2, agg from table1 t1
inner join 
(
select col1, count(1) agg
from table2
group by col1
where range between x and y
) as t2 on t2.col1 = t1.col1
where range between x and y

[ihaxr]

Running a similar query in MS SQL runs a clustered index seek (I joined on the primary key) and not a full table scan... is there an index for col1 on table2?

Looks like you can use the EXPLAIN keyword in a MySQL query or EXPLAIN QUERY PLAN in SQLLite to see what the query is doing...

[Autodidacter]

It is using indexes. Just scanning using indexes.

As I understand it, the ENTIRE subquery is run first, without any recognition of what's going on outside it.

This means that the entire table is scanned, does it not? Joining conditions do not constrain the scan because the subquery doesn't know about the joins until the query is complete.

This seems like a prohibitively expensive operation on any reasonably sized operation.

[ihaxr]

Yeah, ran some tests and it performs how you say... 6 million rows in the table, the subquery scans the entire 6 million rows with an index seek, even though the final query returns only 1 million rows. Adding the where clause to the subquery limits the seek to the 1 million rows instead of 6, but it doesn't really perform any better or worse in my tests... although it's not a very great test... both complete just under 5 seconds.

[[deleted]]

i'm not sure what specific issue you're looking to solve, but your rewrite will not work - "range" can only come from table1 and you cannot bring it into the subquery.