1

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
  • 2
    What DBMS are you using? – ErikE Oct 21 '16 at 16:50
1

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
4

UNION is definitely the way to "stack" rowsets on top of each other, while removing duplicates. (Use UNION ALL to keep duplicates.)

Your queries appear to have some cleanup possible, so we'll do that as well.

Observations:

  1. In the first query, the extra join to UM AS MUM seems unnecessary because no columns are used from that table, and due to the likely uniqueness of UMKEY it won't have multiple matching rows, so there is no effect at all on the final query. I'm removing this join.
  2. The second query's two parts are almost the same and could be combined by using the single join condition ON DF.DFKEY IN (ST.FAMILY, ST.FAMB). Depending on your indexes and other factors, simplifying this way could be okay performance or could be worse. Or, querying twice as you're doing could be best. Instead, I've chosen a way that has at times beaten the performance of both of those for me. (And for what it's worth, my guess is that these two columns represent denormalized data, and refactoring a bit to put the family information into a separate table could be a sensible way to go forward—what if you had FAMC, FAMD, FAME, and so on?)
  3. UM.INTERNATIONAL != 'Y' effectively makes the join to UM an INNER JOIN. So we'll use INNER JOIN to that table.
  4. For what it's worth, the extra and unneeded parentheses add clutter, not clarity (to me, anyway), so I'll be removing them in my answer.
  5. Some of the duplication in each query can be made common, such as the join to UM being the same.
  6. I would encourage you to, where possible, use equality instead of inequality, as it's easier for the mind to comprehend (especially don't use double negatives). If your Y/N columns do not have NULLs and only contain the values Y and N, I recommend switching from OS.DECEASED != 'Y' to OS.DECEASED = 'N' and from UM.INTERNATIONAL != 'Y' TO UM.INTERNATIONAL = 'N'. This is probably merely a matter of style as it likely makes no difference to the query engine (depending on its cardinality estimates and other factors), though there is the possibility that an equality predicate could in some situations perform better than an inequality one.

I believe this query will return exactly the same rowset as you get with the other answers, but it has the potential for running in 1/2 to 1/3rd the time.

SELECT
   UM.UMKEY,
   M.MAILTITLE,
   UM.ADDRESS01,
   UM.ADDRESS02,
   UM.ADDRESS03
FROM
   (
      SELECT
         OS.MAILTITLE,
         OS.HOMEKEY
      FROM
         OS
      WHERE
         OS.OS_EMAIL != ''
         AND OS.DECEASED != 'Y'
         AND OS.GENDER  = 'F'
         AND OS.MAIL_LIST = 'Y'
      UNION
      SELECT
         DF.MAILTITLE,
         DF.HOMEKEY
      FROM
         DF
         INNER JOIN (
            SELECT
               FAMILYKEY = CASE X.WHICH WHEN 0 THEN ST.FAMILY ELSE ST.FAMB END
            FROM
               ST
               CROSS JOIN (SELECT 0 UNION ALL SELECT 1) X (WHICH) 
            WHERE
               ST.STATUS = 'FULL'
         ) ST
            ON DF.DFKEY = ST.FAMILYKEY
   ) M
   INNER JOIN UM
      ON M.HOMEKEY = UM.UMKEY
WHERE
   UM.INTERNATIONAL != 'Y'
ORDER BY
   UM.UMKEY
;

If you are using a DBMS that supports syntax such as CROSS APPLY (SQL Server) or UNPIVOT, the rows from the ST table can be handled in possibly a slightly more efficient manner. For example, the innermost INNER JOIN could look like this in SQL Server:

INNER JOIN (
   SELECT
      F.FAMILYKEY
   FROM
      ST
      CROSS APPLY (VALUES
         (ST.FAMILY),
         (ST.FAMB)
      ) F (FAMILYKEY) 
   WHERE
      ST.STATUS = 'FULL'
) ST
   ON DF.DFKEY = ST.FAMILYKEY
3

Use the UNION keyword again. You almost had it in your second query.

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 -- added this and removed the ORDER BY

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 UMKEY ASC        -- the ORDER BY applies to the whole union, 
  ;                       -- not just the 2nd part

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.