why do I get a row when querying a table with no rows?
(1.1) Originally by Mark Wagner (markxwagner) with edits by Richard Hipp (drh) on 2021-08-18 19:19:47 from 1.0 [link] [source]
I'm trying to decide if this is a bug or a lack of understanding on my part.
This selects on t and joins t2, neither of which has any rows. I would have thought the result would be zero rows. ??
Any help would be appreciated.
sqlite> .nullvalue VIS_NULL sqlite> .dump PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE t (foo); CREATE TABLE t1 (bar); COMMIT; sqlite> select group_concat(quote(bar)) from t left join t1; group_concat(quote(bar)) VIS_NULL sqlite>
(2) By Richard Hipp (drh) on 2021-08-18 19:28:27 in reply to 1.1 [source]
This is a misunderstanding on your part. The group_concat() makes this an aggregate query, and (assuming not GROUP BY or HAVING clauses) aggregate queries always return exactly one row, regardless of the number of input rows.
To get zero rows of return if the input is empty, try this:
SELECT group_concat(quote(bar)) FROM t LEFT JOIN t1 HAVING count(*)>0;