SQL Query – Merging Two Selects and Removing Duplicates

duplicationselect

I have two select statements. Is it possible to merge the two results together and remove duplicates from the final merged result list (as opposed to treating the queries separately).

Query 1

SELECT DISTINCT
    UM.UMKEY,
    OS.MAILTITLE,
    UM.ADDRESS01,
    UM.ADDRESS02,
    UM.ADDRESS03
FROM (OS LEFT JOIN UM ON OS.HOMEKEY = UM.UMKEY) 
LEFT JOIN UM AS MUM ON OS.MAILKEY = MUM.UMKEY
WHERE 
    (OS.OS_EMAIL != '') 
    AND (UM.INTERNATIONAL != 'Y') 
    AND (OS.DECEASED != 'Y') 
    AND (OS.GENDER  = 'F') 
    AND (OS.MAIL_LIST = 'Y')
ORDER BY UM.UMKEY ASC

Query 2

SELECT DISTINCT
    UM.UMKEY,
    DF.MAILTITLE,
    UM.ADDRESS01,
    UM.ADDRESS02,
    UM.ADDRESS03
FROM ST INNER JOIN DF ON (ST.FAMILY = DF.DFKEY) 
INNER JOIN UM ON DF.HOMEKEY = UM.UMKEY
WHERE 
    ST.STATUS = 'FULL'
UNION
SELECT DISTINCT
    UM.UMKEY,
    DF.MAILTITLE,
    UM.ADDRESS01,
    UM.ADDRESS02,
    UM.ADDRESS03
FROM ST INNER JOIN DF ON (ST.FAMB = DF.DFKEY) 
INNER JOIN UM ON DF.HOMEKEY = UM.UMKEY
WHERE ST.STATUS = 'FULL'
ORDER BY UM.UMKEY ASC

Best Answer

Use union between the two queries. The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected. It will also give better performance as you do not need to do a select district on both queries but it will instead get distinct results.

SELECT
UM.UMKEY,
OS.MAILTITLE,
UM.ADDRESS01,
UM.ADDRESS02,
UM.ADDRESS03
FROM (OS LEFT JOIN UM ON OS.HOMEKEY = UM.UMKEY) Left Join UM as MUM On OS.MAILKEY = MUM.UMKEY
WHERE (OS.OS_EMAIL != '') And (UM.INTERNATIONAL != 'Y') And (OS.DECEASED != 'Y') And (OS.GENDER  = 'F') And (OS.MAIL_LIST = 'Y')
UNION
SELECT 
UM.UMKEY,
DF.MAILTITLE,
UM.ADDRESS01,
UM.ADDRESS02,
UM.ADDRESS03
FROM ST INNER JOIN DF ON (ST.FAMILY = DF.DFKEY) INNER JOIN UM ON DF.HOMEKEY = UM.UMKEY
WHERE ST.STATUS = 'FULL'
UNION
SELECT 
UM.UMKEY,
DF.MAILTITLE,
UM.ADDRESS01,
UM.ADDRESS02,
UM.ADDRESS03
FROM ST INNER JOIN DF ON (ST.FAMB = DF.DFKEY) INNER JOIN UM ON DF.HOMEKEY = UM.UMKEY
WHERE ST.STATUS = 'FULL'
ORDER BY UM.UMKEY ASC
Related Question