Find unmatched rows between two tables

Given this setup:

CREATE TABLE table1 (column1 text, column2 text);
CREATE TABLE table2 (column1 text, column2 text);

INSERT INTO table1 VALUES
   ('A', 'A')
 , ('B', 'N')
 , ('C', 'C')
 , ('B', 'A');

INSERT INTO table2 VALUES
   ('A', 'A')
 , ('B', 'N')
 , ('C', 'X')
 , ('B', 'Y');

How can I find missing combinations of (column1, column2) between these two tables? Rows not matched in the other table.

The desired result for the given example would be:

  C | C
  B | A
  C | X
  B | Y

There can be duplicate entries so we’d want to omit those.

Answer

One method is union all:

select t1.col1, t1.col2
from t1
where (t1.col1, t1.col2) not in (select t2.col1, t2.col2 from t2)
union all
select t2.col1, t2.col2
from t2
where (t2.col1, t2.col2) not in (select t1.col1, t1.col2 from t1);

If there are duplicates within a table, you can remove them by using select distinct. There is no danger of duplicates between the tables.