On 2015-07-31 06:22 PM, Gabor Grothendieck wrote:
> 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?
Slow down please! - Your example did work and did get the correct result
using a sub-query AND a CTE. There is no problem with that.
I've simply shown a way to use the "A.avgSalary" notation which you
mentioned would be nice to have, together with a slight optimization
(which I saw mimicked the way John suggested - obviously, he too, moved
to optimize) - but none of those examples mean that sub-queries CANNOT
exist in (or with) CTE's. It simply means that in this specific query,
the CTE is better than the sub-query.
Also, the join is formed with the CTE, I am not sure why you use the
words "prior to forming the CTE".
I hope that is more clear...
Cheers!
Ryan
>
> 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
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
