CODEWITHSUNDEEP
sqlsql-servermergeunion
shockwave
shockwave

Reputation: 3272

SQL UNION and MERGE

I'm having multiple select statements involving many tables and Joins. All the select statements have the same Headers. I'm trying to combine all this into a single result set. So which approach is better SQL UNION or MERGE? I know UNION is A+B. So if a column is NULL in Table A and it has a value in TABLE B then UNION will give me two rows right? So if I want to combine all the rows into a single row based on the id should I use MERGE? I have an option to do this in SQL or SSIS.

SELECT ID, NAME, VitalName as VitalName FROM TABLE A
UNION
SELECT ID, NAME, VitalReadings as VitalName FROM TABLE B

TableA

+----+------+-----------+
| ID | Name | VitalName |
+----+------+-----------+
|  1 | AAA  | HeartRate |
|  2 |      | Systolic  |
|  3 |      | Diastolic |
+----+------+-----------+

TableB

+----+------+---------------+
| ID | Name | VitalReadings |
+----+------+---------------+
|  1 | AAA  | HeartRate     |
|  2 | BBB  | Systolic      |
+----+------+---------------+

Expected Result

+----+------+---------------+
| ID | Name | VitalName     |
+----+------+---------------+
|  1 | AAA  | HeartRate     |
|  2 | BBB  | Systolic      |
|  3 |      | Diastolic     |
+----+------+---------------+

Upvotes: 4

Views: 22440

Answers (3)

jarlh
jarlh

Reputation: 44766

GROUP BY the UNION's result. Use MAX() to return the NAME:

select ID, MAX(NAME), VitalName 
from
(
    SELECT ID, NAME, VitalName as VitalName FROM TABLE A
    UNION ALL
    SELECT ID, NAME, VitalReadings as VitalName FROM TABLE B
) dt
group by ID, VitalName

Upvotes: 5

Serkan Arslan
Serkan Arslan

Reputation: 13393

UNION and MERGE totally different concepts and both not solves your problem. But can use FULL JOIN for making this. 

DECLARE @TableA TABLE (ID INT,  Name VARCHAR(10), VitalName VARCHAR(10))
INSERT INTO @TableA VALUES
(1,'AAA','HeartRate'),
(2,NULL,'Systolic'),
(3,NULL,'Diastolic')

DECLARE @TableB TABLE ( ID INT,  Name VARCHAR(10), VitalReadings VARCHAR(10))

INSERT INTO @TableB VALUES
(1,'AAA','HeartRate'),
(2,'BBB','Systolic')


SELECT 
    A.ID, 
    COALESCE(A.Name, B.Name) Name,
    COALESCE(A.VitalName, B.VitalReadings) VitalName
FROM 
    @TableA A 
    FULL JOIN @TableB B ON A.ID = B.ID

Result:

ID          Name       VitalName
----------- ---------- ----------
1           AAA        HeartRate
2           BBB        Systolic
3           NULL       Diastolic

Upvotes: 8

Robert
Robert

Reputation: 486

Use union and then use select distinct to remove duplicate.

e.g. SELECT DISTINCT * FROM (SELECT ID, NAME, VitalName as VitalName FROM TABLE_A UNION SELECT ID, NAME, VitalReadings as VitalName FROM TABLE_B) as TABLE_C

Upvotes: 1

Related Questions