https://www.youtube.com/watch?v=J9wwR4huimI&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=20

-- Create the source table
CREATE TABLE source (
    id INT,
    name VARCHAR(5)
);

-- Create the target table
CREATE TABLE target (
    id INT,
    name VARCHAR(5)
);

-- Insert values into the source table
INSERT INTO source (id, name) VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (4, 'D');

-- Insert values into the target table
INSERT INTO target (id, name) VALUES
    (1, 'A'),
    (2, 'B'),
    (4, 'X'),
    (5, 'F');

-- Approach 1
SELECT Source_id AS id,'Mismatch' AS Comment FROM(
SELECT s.id AS Source_id,s.name AS Source,t.id AS Target_id,t.name AS Target FROM source AS s,target AS t
WHERE s.id = t.id AND s.name != t.name) mm

UNION ALL 

SELECT Source_id AS id,'New in Source' AS Comment FROM(
SELECT s.id AS Source_id,s.name AS Source,t.id AS Target_id,t.name AS Target FROM source AS s LEFT JOIN target AS t
ON s.id = t.id
WHERE t.name is NULL) ns 

UNION ALL

SELECT target_id AS id,'New in Target' AS Comment FROM(
SELECT s.id AS Source_id,s.name AS Source,t.id AS Target_id,t.name AS Target FROM source AS s Right JOIN target AS t
ON s.id = t.id
WHERE s.name is NULL) nt	

WITH

-- Approach 2

WITH CTE AS (
SELECT *,'Source' AS tablename FROM source
UNION ALL
SELECT *,'Target' AS tablename FROM target
)
SELECT  id,
CASE WHEN min(name) != max(name) THEN 'Mismatch' WHEN min(tablename) = 'Source' THEN 'New in Source' ELSE 'New in target' END AS Comment
FROM CTE 
GROUP BY id
HAVING COUNT(*) = 1 OR (COUNT(*) = 2 AND min(name) != max(name));