On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin <slavins at bigfraud.org> wrote:
> > I am noting an overlap with the things SQLite users have been using > sub-SELECTs for. > > 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.)
