Union by single column

Question

I have 2 tables in different databases and I want to be able to select all rows from names_new and the missing ones from names and would like to know where each one comes from.

+---------------+              +---------------+ 
|     NAMES     |              |   NAMES_NEW   |
+---------------+              +---------------+
| ID |   NAME   |              | ID |   NAME   |
+----+----------+              +----+----------+
|  1 |  Name1   |              |  1 |  Name1!  |
+----+----------+              +----+----------+
|  2 |  Name2   |              |  3 |  Name3   |
+----+----------+              +----+----------+

              +----------------------+ 
              |       RESULTS        |  <-- I want this result
              +----------------------+ 
              | ID |   NAME   | FROM | 
              +----+----------+------+
              |  1 |  Name1!  |  NEW |
              +----+----------+------+
              |  2 |  Name2   |  OLD |
              +----+----------+------+
              |  3 |  Name3   |  NEW |
              +----+----------+------+

Something like this, but that actually works

(
  SELECT
    n.ID as [ID],
    n.NAME as [NAME],
    'OLD' AS [FROM]
  FROM NAMES n
  UNION
  SELECT
    nn.ID as [ID],
    nn.NAME as [NAME],
    'NEW' as [FROM]
  FROM NAMES_NEW nn
)
ORDER BY NAME ASC OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

I'm gonna show this in a webpage. So I will need to be able to add pagination and different where clauses.

I have created a JDoodle here: https://jdoodle.com/a/U9k

Answer 1

I would use union all like this:

select nn.id, nn.name, 'new' as which
from names_new nn
union all
select n.id, n.name, 'old' as which
from names_old n
where not exists (select 1 from names_new nn where nn.id = n.id);

This compares the ids. I'm not sure if you want to compare the ids or the names (or both) for prioritization purposes.