Combining two similar queries into one (PostgreSQL)

Question

I have 2 queries like this:

SELECT c.id FROM component c, base_vee bv
    WHERE
        c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) 
        AND
        c.id = bv.component_id
        AND 
        (bv.affidavit IS NULL OR bv.affidavit = '')


SELECT c.id FROM component c, base_vee bv
    WHERE
        c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) 
        AND
        c.id = bv.component_id
        AND 
        (bv.affidavit IS NOT NULL AND bv.affidavit != '')

What I need to get is a list of component IDs with False if bv.affidavit is emptyish or True if it's not emptyish.

Right now I'm obviously getting two lists of component IDs for each case.

It works, but one query like described above would be nicer.

Answer 1

Use the CASE function:

SELECT c.id,
       CASE WHEN COALESCE(bv.affidavit, '') = '' THEN False
            ELSE True
       END
FROM   component c, base_vee bv
WHERE  c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199) 
AND    c.id = bv.component_id;

Answer 2

COALESCE will let you get away with a lot of interesting things when you're dealing with the situation of "if it's null do one thing, otherwise do something else"..

SELECT
  c.id, CASE WHEN COALESCE(bv.affidavit, '') = '' THEN FALSE ELSE TRUE END AS emptyish
FROM component c
JOIN base_vee bv ON c.id = bv.component_id
WHERE c.id IN (SELECT component_id FROM registry_row WHERE registry_id = 199)

COALESCE takes the first not-null argument to use, so if bv.affidavit happens to be NULL it will use '' instead.. I shouldn't need to test for it being NOT NULL in the follow up..