CODEWITHSUNDEEP
sqlsql-serverunion
Jeggy
Jeggy

Reputation: 1650

Union by single column

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

Upvotes: 1

Views: 472

Answers (2)

Thom A
Thom A

Reputation: 95554

I don't see any need for a UNION here, I think a FULL OUTER JOIN would likely provide a better solution:

SELECT ISNULL(NN.ID,N.ID) AS ID,
       ISNULL(NN.[NAME], N.[NAME]) AS [Name],
       CASE WHEN NN.ID IS NOT NULL THEN 'NEW' ELSE 'OLD' END AS [From]
FROM dbo.NAMES N
     FULL OUTER JOIN dbo.NAMES_NEW NN ON N.ID = NN.ID;

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 1

Related Questions