Checking for different columns OR empty result PostgreSQL

Question

I'm writing some query tests for dbt and I'd like to check that two queries return the same thing and that neither is empty. Since it's a dbt test, I need to return a row if either of these conditions is false. So far I've come up with the following:

with query1 as (
    select id from table1
),
query2 as (
    select id from table2
)
select
    case
        -- if either query returns 0 rows, return 1
        when not exists(select * from query1) OR not exists(select * from query2) then 1

        -- if both queries aren't empty, check for different columns and return their id's
        else COALESCE(query1.id, query2.id) END as id
from query1
FULL JOIN query2
    ON query1.id = query2.id
WHERE query1 IS NULL OR query2 IS NULL

Theoretically, this should return a row with 1 if either query is empty and if neither are then it should return any columns that don't belong to BOTH queries. When only query1 is empty or only query 2 is empty this works but if both are empty then it returns an empty table instead of 1. My hunch is that it's something to do with the JOIN but I really don't know enough about SQL to figure out why that's affecting the original queries.

EDIT: Adding examples

No different rows

query1:      query2:      result:
|  id   |   |  id   |    |  id   |
|-------|   |-------|    |-------|
|   1   |   |   3   |    |       |
|   3   |   |   1   |

Different rows

query1:      query2:      result:
|  id   |   |  id   |    |  id   |
|-------|   |-------|    |-------|
|   1   |   |   4   |    |   3   |
|   3   |   |   1   |    |   4   |

Either table empty

query1:      query2:      result:
|  id   |   |  id   |    |  id   |
|-------|   |-------|    |-------|
|       |   |       |    |   1   |
|       |   |       |

Post sample data and expected results to clarify what you want. — forpas
– forpas, Commented Jul 13, 2021 at 15:26
If "either" table is empty but not both, then the ids in the other table should be returned. If both are empty, then the empty set is the correct result logically for what you are asking. — Gordon Linoff
– Gordon Linoff, Commented Jul 13, 2021 at 15:43
What is zone_1 and zone_1 in your query? Also why do you want 1 row with id = 1 if either table is empty? — forpas
– forpas, Commented Jul 13, 2021 at 15:44
@GordonLinoff That would be the case for a regular query but I'm writing this as a DBT test so I need to return something if either table is empty. — shaye059
– shaye059, Commented Jul 13, 2021 at 15:48
@forpas I simplified it a little from the original and forgot to change that to query1 and query2. It doesn't matter too much what I return but I need to return a result since the test would therefore fail — shaye059
– shaye059, Commented Jul 13, 2021 at 15:49

forpas · Accepted Answer · 2021-07-13 16:33:53Z

1

I propose that the query returns a value like 0 in case any of the tables is empty because this can be distinguished form a valid id value of 1.

So, use UNION ALL for this case and check with EXISTS if any of the tables is empty:

WITH 
  query1 AS (SELECT id from table1),
  query2 AS (SELECT id from table2)
SELECT 0 id
WHERE (NOT EXISTS (SELECT 1 FROM query1)) OR (NOT EXISTS (SELECT 1 FROM query2))
UNION ALL
SELECT COALESCE(q1.id, q2.id) id
FROM query1 q1 FULL JOIN query2 q2
ON q2.id = q1.id
WHERE (q1.id IS NULL OR q2.id IS NULL)
  AND (EXISTS (SELECT 1 FROM query1)) AND (EXISTS (SELECT 1 FROM query2))

Note that for the sample data you posted you don't really need the CTEs because you can select directly from the tables.

See the demo.

answered Jul 13, 2021 at 16:33

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Checking for different columns OR empty result PostgreSQL

EDIT: Adding examples

No different rows

Different rows

Either table empty

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

EDIT: Adding examples

No different rows

Different rows

Either table empty

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related