The key is the scalar subquery. A scalar subquery which selects no rows returns NULL.
https://sqlfiddle.com/postgresql/online-compiler?id=e439059a-d46d-4d49-b8ab-9ff533656066 On Tue, Dec 9, 2025, 5:33 PM Thiemo Kellner <[email protected]> wrote: > > On 12/9/25 18:29, David G. Johnston wrote: > > On Tue, Dec 9, 2025 at 10:14 AM Thiemo Kellner > > <[email protected]> wrote: > > > > I feel, you meant to say, the subquery does not return any record > > which is not the same as returns NULL. > > > > > > For a scalar subquery the final output of a zero-row query is the null > > value. > > > To me, it does not look like that (please note the empty line in the > last example). Can you point me to the documentation saying that 0 rows > is sometimes equal to 1 row? > > postgres=# select * from pg_user; > usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls > | passwd | valuntil | useconfig > ----------+----------+-------------+----------+---------+--------------+----------+----------+----------- > > > postgres | 10 | t | t | t | t | > ******** | | > (1 row) > > postgres=# select usename from pg_user where false; > usename > --------- > (0 rows) > > postgres=# select null as usename from pg_user; > usename > --------- > > (1 row) > > >
