CODEWITHSUNDEEP
sqlpostgresql
aniani2020
aniani2020

Reputation: 143

Output a boolean in Postgresql

I want to write a query in PostgreSQL that outputs true if a table is a sub-table of another given table, consider the following example:

    TABLE A                TABLE B
   --x: int--            --x: int--
       2                      2
       3                      3
       4

An idea of query was something like this:

NOT EXISTS(SELECT 1 FROM B b WHERE b.x NOT IN (SELECT x FROM A))

The inner SELECT returns no rows so the NOT EXISTS should be TRUE, but this returns an error, what am I doing wrong?

Upvotes: 0

Views: 155

Answers (2)

user330315
user330315

Reputation:

You simply forgot to start your query with SELECT

select NOT EXISTS(SELECT 1 FROM B b WHERE b.x NOT IN (SELECT x FROM A));

I would use the EXCEPT operator for that:

select count(*) = 0
from (
  select *
  from b
  except 
  select *
  from a
) x

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

How about using JOIN?

select (case when count(*) = count(a.x) then 'SUBSET' else 'NOT SUBSET' END)
from b left join
     a
     on b.x = a.x

Upvotes: 0

Related Questions