On Sunday, 17 March, 2019 11:19, niklas <nikba...@gmail.com> wrote:
>I agree that correlated subqueries in general seem more natural and
>are probably also less likely to have the performance pessimizations
>noticed with joins.
>But I might also want to use the column, or in case of a correlated
>subquery, the column alias, in the WHERE clause and previously that
>has not been supported as I recall. Maybe also not allowed by SQL
>specification itself?
>I modified the suggested query and used the column alias in WHERE now
>though, and it seemed to work!
Yes and no and it "seemed to work" is an adequate description. You have to
realize that the list of things to get (the SELECT arguments) are executed only
after the WHERE clause is executed. That is to say, the semantics of the
SELECT statement is:
SELECT some stuff
FROM <a bunch of tables>
WHERE <a bunch of conditions to find the intersection of the bunch of tables>
so that except in the case where the alias in the select list is a simple alias
for a column name, the computation will be made twice. So for example if you
did something like:
select BookID,
Title,
(
SELECT group_concat(ltrim("First Name" || ' ' || "Last Name"),',')
FROM AuthorBooks JOIN Authors USING (AuthorID)
WHERE BookID == Books.BookID
) as "Author(s)",
(
SELECT group_concat("Date read",', ')
FROM DatesRead
WHERE BookID == Books.BookID
) as "Date(s)",
(
SELECT group_concat(Genre,', ')
FROM BookGenres JOIN Genres USING (GenreID)
WHERE BookID == Books.BookID
) AS "Genre(s)"
FROM Books
WHERE "Author(s)" IN NOT NULL
AND "Date(s)" IS NOT NULL
AND "Genre(s)" IS NOT NULL
ORDER BY BookID;
then you are executing the correlated subquery's TWICE each, once for the WHERE
clause and once for the SELECT clause. If you want to ensure that those values
are not null, and do not want to execute the correlates twice, you need to do
something like this which will execute the correlates only for the books that
would not have null results is those three columns (without doing the duplicate
group_concat).
But the inner join of the subqueries will still be more performant IF THE QUERY
PLANNER USES AUTOMATIC INDEXES.
select BookID,
Title,
(
SELECT group_concat(ltrim("First Name" || ' ' || "Last Name"),',')
FROM AuthorBooks JOIN Authors USING (AuthorID)
WHERE BookID == Books.BookID
) as "Author(s)",
(
SELECT group_concat("Date read",', ')
FROM DatesRead
WHERE BookID == Books.BookID
) as "Date(s)",
(
SELECT group_concat(Genre,', ')
FROM BookGenres JOIN Genres USING (GenreID)
WHERE BookID == Books.BookID
) AS "Genre(s)"
FROM Books
WHERE BookID in (
SELECT BookID FROM AuthorBooks JOIN Authors USING
(AuthorID)
INTERSECT
SELECT BookID FROM DatesRead
INTERSECT
SELECT BookID FROM BookGenres JOIN Genres USING (GenreID)
)
ORDER BY BookID;
>Is this a recent change in Sqlite or have I misunderstood something?
>The Sqlite documentation still does not seem to say that column aliases
>can be used in the WHERE clause at least.
Well, I think this was added somewhere along the way. Remember they are
ALIASES and the original text is substituted for them.
Of course, you could always just retrieve all the data and ignore the rows you
do not want at the application level ...
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
