How to compare two snaps of schema data in SQL?

I have two tables:

Current_Ranking:

id   rank
1     20
2     25
3     26
4     17

Previous_Ranking

id rank
1   20
2   26
3   18
4   17
5   5

I want to get as a result all records in Previous_Ranking that don’t appear in Current_Ranking (means new id) and all records that their rank in Previous_Ranking is not the same as Current_Ranking

So expected result is:

id rank
2   26
3   18
5   5

How can I do that? I know I can do:

    SELECT   p.id, p.rank
    FROM     Previous_Ranking p
       LEFT JOIN Current_Ranking c USING (id)
    WHERE    c.id IS NULL

This should give me all the new rows. But How I continue from here?

I’m using BigQuery so this can be done with native SQL.

Answer

You may use a left join with two criteria:

SELECT p.id, p.rank
FROM Previous_Ranking p
LEFT JOIN Current_Ranking c
    ON p.id = c.id
WHERE
    c.id IS NULL OR p.rank <> c.rank;

Note: RANK is a reserved keyword in many versions of SQL (though apparently not in BigQuery). So, you might want to avoid using RANK as a name for columns and tables.