Re: How do I check for NULL

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 <thiemo(at)gelassene-pferde(dot)biz>
wrote:

>
> On 12/9/25 18:29, David G. Johnston wrote:
> > On Tue, Dec 9, 2025 at 10:14 AM Thiemo Kellner
> > <thiemo(at)gelassene-pferde(dot)biz> 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)
>
>
>