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