SQL Check if two columns from two tables are completely disjoint

681 views Asked by At

I have two tables

CREATE TABLE A
    ID   INT   PRIMARY KEY
    ....

CREATE TABLE B
    ID   INT   PRIMARY KEY
    ....

How do I check if A.ID and B.ID are disjoint using postgres.

  • Disjoint meaning there is no value in B.ID that exists in A.ID and vice versa.
  • I want to return a boolean.

Here's an example

/* Returns False */
A.ID: 4, 5, 6, 7
B.ID: 5, 7, 8, 9

/* Returns True */
A.ID: 1, 2, 3, 4
B.ID: 5, 6, 7, 8

Any help would be appreciated!

Thank you

2

There are 2 answers

2
GMB On BEST ANSWER

If you want to ensure the tables have no id in common, you can do:

select bool_and(a.id is distinct from b.id) res
from a
full join b on a.id = b.id

This returns a boolean flag that is set to true if the tables have no common id.

0
SOHAM N. On

Here's another way to solve this problem :

SELECT
    CASE 
        WHEN (  SELECT
                    COUNT(*) 
                FROM
                    A 
                WHERE
                    ID IN ( SELECT
                                * 
                            FROM
                                B
                    ))>0 
        THEN FALSE 
        ELSE TRUE 
    END