Both the solutions transformed the correlated subquery into a join prior to
forming the CTE. Can we conclude, in general, that CTEs do not support
correlated subqueries?
On Fri, Jul 31, 2015 at 11:30 AM, R.Smith <rsmith at rsweb.co.za> wrote:
>
>
> On 2015-07-31 03:40 PM, Gabor Grothendieck wrote:
>
>> 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:
>>
>
> Well, you can, sort-of, if you use the CTE column naming. Consider this
> simplified version of your Query (also using the CTE to do the actual
> aggregate work once only):
>
> create table Emp (emp text, salary real, dept text);
> insert into Emp values
> ('a', 1, 'A'),
> ('b', 2, 'A'),
> ('c', 3, 'A'),
> ('d', 1, 'B'),
> ('e', 2, 'B'),
> ('f', 3, 'B');
>
>
> WITH A(dept,avgSalary) as (
> SELECT Emp.dept, AVG(salary) FROM Emp GROUP BY Emp.dept
> )
> SELECT Emp.*
> FROM Emp, A
> WHERE Emp.dept = A.dept AND Emp.salary > A.avgSalary;
>
>
> -- emp | salary | dept
> -- ----- | ------ | -----
> -- c | 3.0 | A
> -- f | 3.0 | B
>
>
> -- 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.)
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
