sqlpostgresqlcountfull-outer-joindisjoint-sets

SQL Check if two columns from two tables are completely disjoint


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.

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


Solution

  • 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.