How to find the different values between 2 tables-CodePudding

I have 2 tables and I want to find only the different values in 2 columns on one of the tables (only the unique combination of 2 strings that is not in the first table )

for example : TABEL 1

ID1	ID2
X1	X2
X3	X4
X5	X6
X3	X4
X6	X7

TABEL 2 (the table I want to take the values from that are not in table1 )

ID1	ID2
X1	X2
X3	X4
X5	X6
X4	X3
X1	X9
X3	X12

the output will be :

ID1	ID2
X1	X9
X3	X12

CodePudding user response：

You can use NOT EXISTS:

SELECT t2.*
FROM Table2 t2
WHERE NOT EXISTS (
            SELECT *
            FROM Table1 t1
            WHERE (t1.ID1 = t2.ID1 AND t1.ID2 = t2.ID2)
               OR (t1.ID1 = t2.ID2 AND t1.ID2 = t2.ID1)
          );

or:

SELECT t2.*
FROM Table2 t2
WHERE NOT EXISTS (
            SELECT *
            FROM Table1 t1
            WHERE LEAST(t1.ID1, t1.ID2) = LEAST(t2.ID1, t2.ID2)
              AND GREATEST(t1.ID1, t1.ID2) = GREATEST(t2.ID1, t2.ID2)
          );

or:

SELECT t2.*
FROM Table2 t2
WHERE NOT EXISTS (
            SELECT *
            FROM Table1 t1
            WHERE (t1.ID1, t1.ID2) IN ((t2.ID1, t2.ID2), (t2.ID2, t2.ID1))
          );

or, with MINUS:

SELECT ID1, ID2 FROM Table2
MINUS
SELECT ID1, ID2 FROM Table1
MINUS 
SELECT ID2, ID1 FROM Table1;

See the demo.

CodePudding user response：

One option uses exists logic:

SELECT t2.ID1, t2.ID2
FROM Table2 t2
WHERE NOT EXISTS (
    SELECT 1
    FROM Table1 t1
    WHERE t1.ID1 = t2.ID1 AND
          t1.ID2 = t2.ID2
);

We can also use a left anti-join:

SELECT t2.ID1, t2.ID2
FROM Table2 t2
LEFT JOIN Table1 t1
    ON t1.ID1 = t2.ID1 AND
       t1.ID2 = t2.ID2
WHERE t1.ID1 IS NULL;

CodePudding user response：

SELECT T2.ID1,T2.ID2
FROM TABLE2 T2
MINUS
SELECT T1.ID1,T1.ID2
FROM TABLE1 T1