On Fri, Jul 31, 2015 at 8:40 AM, Gabor Grothendieck <ggrothendieck at gmail.com > wrote:
> > > Here is a self contained example that can be fed into the sqlite3 command > line tool. It uses WITH to factor out the subquery; however, the annoying > part about it is that even though we have factored out the subquery we > still have to include a simplified version of the subquery, viz. > (select avgSalary from A) > It would have been nice if we could just replace (select avgSalary from A) > with (A.avgSalary) or even (A) but those substitutions do not work: > > -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery > ?? > create table Emp (emp text, salary real, dept text); > insert into Emp values ('a', 1, 'A'); > insert into Emp values ('b', 2, 'A'); > insert into Emp values ('c', 3, 'A'); > insert into Emp values ('d', 1, 'B'); > insert into Emp values ('e', 2, 'B'); > insert into Emp values ('f', 3, 'B'); > WITH A as > ?? > (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept) > ?? > SELECT * > FROM Emp AS e1 > WHERE salary > (select avgSalary from A); > > Here is a sample run assuming the above has been placed into a file named > test.sqlite. > > C:\> sqlite3 < test.sqlite > c|3.0|A > f|3.0|B > > (I am using sqlite 3.8.3 on Windows 8.1.) ?Using SQLite 3.8.11 on Linux (RedHat Fedora 22), I used the following. I don't know that it is "better", but doesn't use sub-selects, if that is of any use. sqlite> WITH a AS ...> (SELECT dept, AVG(salary) AS avgSalary FROM Emp GROUP BY dept) ...> select Emp.emp, salary, Emp.dept ...> FROM Emp JOIN a ON Emp.dept = a.dept AND salary > avgSalary; c|3.0|A f|3.0|B I did an EXPLAIN QUERY PLAN on both of those. But I don't understand, at all, what I am being told. I have posted the output for each, in the hopes that somebody will tell me which is better. >From the original, using the sub-select 0|0|0|SCAN TABLE Emp AS e1 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SCAN TABLE Emp >From my alternate, using the JOIN with no sub-select 1|0|0|SCAN TABLE Emp 1|0|0|USE TEMP B-TREE FOR GROUP BY 0|0|0|SCAN TABLE Emp 0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (dept=?) ? -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
