sql query to return differences between two tables

(   SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2)  
UNION ALL
(   SELECT * FROM table2
    EXCEPT
    SELECT * FROM table1) 

To get all the differences between two tables, you can use like me this SQL request :

SELECT 'TABLE1-ONLY' AS SRC, T1.*
FROM (
      SELECT * FROM Table1
      EXCEPT
      SELECT * FROM Table2
      ) AS T1
UNION ALL
SELECT 'TABLE2-ONLY' AS SRC, T2.*
FROM (
      SELECT * FROM Table2
      EXCEPT
      SELECT * FROM Table1
      ) AS T2
;

I know that this may not be a popular answer but I do agree with @Randy Minder on using third party tool when more complex comparison is needed.

This specific case here is easy and for this case such tools are not needed but this can get complex easily if you introduce more columns, databases on two servers, more complex comparison criteria and such.

There are a lot of these tools such as ApexSQL Data Diff or Quest Toad and you can always use them in trial mode to get the job done.

IF you have tables A and B, both with colum C, here are the records, which are present in table A but not in B:

SELECT A.*
FROM A
    LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL

To get all the differences with a single query, a full join must be used, like this:

SELECT A.*, B.*
FROM A
    FULL JOIN B ON (A.C = B.C)
WHERE A.C IS NULL OR B.C IS NULL

What you need to know in this case is, that when a record can be found in A, but not in B, than the columns which come from B will be NULL, and similarly for those, which are present in B and not in A, the columns from A will be null.