In SQLite, how do I query the changed values in two tables, and NULL out the matching values

Question

If I have two rows in two different tables such as:

Table Name - Old

id	title	views
wx	How to clean a drill	30
np	Craziest Fails	400
zo	Eating Challenge	8
lf	JavaScript Tutorial	0

Table Name - New

id	title	views
wx	How to clean a drill	30
np	Craziest Fails	450
zo	This was a mistake	8
lf	Learning JavaScript	20

The differences in the two tables are as follows:

• Row 1 (id=wx): title and views have not changed.
• Row 2 (id=np): The views has increased, while the title is the same.
• Row 3 (id=zo): The title has changed, while the views are the same.
• Row 4 (id=lf): Both title and views have changed.

Desired Result

I want a query that returns Table Name - New, but any values that haven't changed from Table Name - Old should be null, other than id. If the entire row has not changed, then the row should not be returned.

id is constant and does not change.

Query Result

id	title	views
np	null	450
zo	This was a mistake	null
lf	Learning JavaScript	20

The closest I have gotten is

SELECT * FROM new EXCEPT SELECT * FROM old;

but this does not null out the unchanged values.

Would be grateful for any help.

Answer 1

Join the tables and check if the corresponding columns are different:

SELECT o.id,
       NULLIF(n.title, o.title) title,
       NULLIF(n.views, o.views) views
FROM Old o INNER JOIN New n
ON n.id = o.id
WHERE n.title <> o.title OR n.views <> o.views;

If the columns title and views may contain nulls then use IS NOT to compare them:

SELECT o.id,
       NULLIF(n.title, o.title) title,
       NULLIF(n.views, o.views) views
FROM Old o INNER JOIN New n
ON n.id = o.id
WHERE n.title IS NOT o.title OR n.views IS NOT o.views

See the demo.

Answer 2

I think this is a case for NULLIF function. From the docs:

The nullif(X,Y) function returns its first argument if the arguments are different and NULL if the arguments are the same. The nullif(X,Y) function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. If neither argument to nullif() defines a collating function then the BINARY is used.

So you can JOIN both tables on the id and pass every column through NULLIF

SELECT
    new.id
    , NULLIF(new.title, old.title)
    , NULLIF(new.views, old.views)
FROM
    new
    JOIN old ON new.id = old.id
;