How to find different rows in two tables with same columns?

Question

I have two tables that have exact same set of columns. I'd like to select all rows that don't exactly match. Is there a way to do that without joining by every column or typing every column's name in any other way (I have a large number of them)?

Answer 1

If the number, type and order of columns are exactly the same, you can use the EXCEPT (or in some DBMS MINUS) operator to remove all rows from the first table, that match a row from the second table (by every column).

SELECT *
       FROM table1
EXCEPT
SELECT *
       FROM table2;

(Use EXCEPT ALL, if you don't want or need duplicate elimination. If you want also the result when the operands are interchanged, you can use UNION (or UNION ALL to union the results of a second EXCEPT operation. In doubt use parenthesis to prioritize the operations as needed.)

Answer 2

use minus

select * from tableA
   minus
   select * from tableB

If the query returns no rows then the data is exactly the same.

Answer 3

You could use JOIN by PK and compare all other columns using:

SELECT *
FROM src s
FULL OUTER JOIN trg t
  ON s.id = t.id
WHERE NOT EXISTS (SELECT s.col1, s.col2, s.col3, s.col4
                  INTERSECT
                  SELECT t.col1, t.col2, t.col3, t.col4);

Please note that this approach allows to compare data side-by-side.

DBFiddle Demo

---

EDIT:

That still requires to explicitly mention every column? I'd rather not to.

Yes, but you could use drag and drop from object explorer(SSMS/TOAD/Oracle Developer) and avoid manually typing them.

There is SELECT * EXCEPT(only Google Big Query):

SELECT *
FROM src s
FULL OUTER JOIN trg t
  ON s.id = t.id
WHERE NOT EXISTS (SELECT s.* EXCEPT s.id
                  INTERSECT
                  SELECT t.* EXCEPT t.id);