How do I select null if no rows match in PostgreSQL?

I have the following query:

SELECT c.customer_id
FROM order o
JOIN customer c USING (customer_id)
WHERE c.time >= '2021-01-01 10:00:00' AND c.time < '2021-01-01 11:00:00'
    AND (0 IN (22) OR o.product_id IN (22))
LIMIT 1

As long as there is at least one match, one row will be returned, however if there is no match, then there will not be any rows.

Is it possible to return one row with null value if there is no match?

Solution

One solution is a LEFT JOIN, but starting with the customers table. However, it is a little complicated:

SELECT o.customer_id
FROM customer c LEFT JOIN
     order o
     ON c.customer_id = o.customer_id AND
        o.time >= '2021-01-01 10:00:00' AND
        o.time < '2021-01-01 11:00:00'
LIMIT 1;

Note that this is returning customer_id from the orders table. That is how it is NULL if there is no match.

The above does assume that you have at least one customer, which seems like a reasonable assumption.

An alternative is UNION ALL:

WITH c as (
      SELECT customer_id
      FROM customer c JOIN
           order o
           USING (customer_id) 
      WHERE o.time >= '2021-01-01 10:00:00' AND
            o.time < '2021-01-01 11:00:00'
      LIMIT 1
     )
SELECT c.customer_id
FROM c
UNION ALL
SELECT NULL
WHERE NOT EXISTS (SELECT 1 FROM c);

And a third alternative might be even simpler, a subquery:

select (select o.customer_id
        from order o
        where o.time >= '2021-01-01 10:00:00' AND
              o.time < '2021-01-01 11:00:00'
        limit 1
       ) as customer_id;

If the subquery returns no rows, then the result is NULL.