How to avoid duplicate records while using a UNION - SOLVED
-
I have two SELECT statements joined with a UNION keyword. There's a problem if both SELECT statements return the same record thus causing it to appear twice in the result set. Is there any easy way to make the two SELECT statements mutually exclusive, so that if a record appears in the first one, it should not appear in the second one?
SELECT Column1, Column2, (SUBQUERY1) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUEUNION
SELECT Column1, Column2, (SUBQUERY2) [SOMEDIFFERENTDATA]
FROM TABLE1
WHERE CRITERIA2 IS TRUESOLUTION: (This solution brought to you by rubber duck debugging.)
SELECT Column1, Column2, COALESCE((SUBQUERY2), (SUBQUERY1)) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUE
OR CRITERIA2 IS TRUEThe difficult we do right away... ...the impossible takes slightly longer.
-
I have two SELECT statements joined with a UNION keyword. There's a problem if both SELECT statements return the same record thus causing it to appear twice in the result set. Is there any easy way to make the two SELECT statements mutually exclusive, so that if a record appears in the first one, it should not appear in the second one?
SELECT Column1, Column2, (SUBQUERY1) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUEUNION
SELECT Column1, Column2, (SUBQUERY2) [SOMEDIFFERENTDATA]
FROM TABLE1
WHERE CRITERIA2 IS TRUESOLUTION: (This solution brought to you by rubber duck debugging.)
SELECT Column1, Column2, COALESCE((SUBQUERY2), (SUBQUERY1)) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUE
OR CRITERIA2 IS TRUEThe difficult we do right away... ...the impossible takes slightly longer.
Depending on your data,
COALESCEis probably not the right approach. You unconditionally execute subquery 2, and only execute subquery 1 if #2 returnsNull. That's a change in behaviour from your original query. You should probably use aCASEstatement instead:SELECT Column1, Column2, CASE WHEN CRITERIA1 IS TRUE THEN (SUBQUERY1) ELSE (SUBQUERY2) END As SomeData
FROM TABLE1
WHERE CRITERIA1 IS TRUE
OR CRITERIA2 IS TRUEIf you want to prioritize criteria 2 matches, then swap the case around:
CASE WHEN CRITERIA2 IS TRUE THEN (SUBQUERY2) ELSE (SUBQUERY1) END As SomeData
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
"These people looked deep within my soul and assigned me a number based on the order in which I joined" - Homer
-
Depending on your data,
COALESCEis probably not the right approach. You unconditionally execute subquery 2, and only execute subquery 1 if #2 returnsNull. That's a change in behaviour from your original query. You should probably use aCASEstatement instead:SELECT Column1, Column2, CASE WHEN CRITERIA1 IS TRUE THEN (SUBQUERY1) ELSE (SUBQUERY2) END As SomeData
FROM TABLE1
WHERE CRITERIA1 IS TRUE
OR CRITERIA2 IS TRUEIf you want to prioritize criteria 2 matches, then swap the case around:
CASE WHEN CRITERIA2 IS TRUE THEN (SUBQUERY2) ELSE (SUBQUERY1) END As SomeData
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Hi Richard, Thanks for your response. I should have specified that if NULL is returned from either of the subqueries, then the whole record should be ignored. I appreciate your analysis. Thanks!
The difficult we do right away... ...the impossible takes slightly longer.
-
I have two SELECT statements joined with a UNION keyword. There's a problem if both SELECT statements return the same record thus causing it to appear twice in the result set. Is there any easy way to make the two SELECT statements mutually exclusive, so that if a record appears in the first one, it should not appear in the second one?
SELECT Column1, Column2, (SUBQUERY1) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUEUNION
SELECT Column1, Column2, (SUBQUERY2) [SOMEDIFFERENTDATA]
FROM TABLE1
WHERE CRITERIA2 IS TRUESOLUTION: (This solution brought to you by rubber duck debugging.)
SELECT Column1, Column2, COALESCE((SUBQUERY2), (SUBQUERY1)) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUE
OR CRITERIA2 IS TRUEThe difficult we do right away... ...the impossible takes slightly longer.
Hi. You must explicitly use UNION ALL when you need duplicate. If you using UNION already remove duplicates.
-
I have two SELECT statements joined with a UNION keyword. There's a problem if both SELECT statements return the same record thus causing it to appear twice in the result set. Is there any easy way to make the two SELECT statements mutually exclusive, so that if a record appears in the first one, it should not appear in the second one?
SELECT Column1, Column2, (SUBQUERY1) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUEUNION
SELECT Column1, Column2, (SUBQUERY2) [SOMEDIFFERENTDATA]
FROM TABLE1
WHERE CRITERIA2 IS TRUESOLUTION: (This solution brought to you by rubber duck debugging.)
SELECT Column1, Column2, COALESCE((SUBQUERY2), (SUBQUERY1)) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUE
OR CRITERIA2 IS TRUEThe difficult we do right away... ...the impossible takes slightly longer.
Howsabout... SELECT Column1, Column2, (SUBQUERY1) [SOMEDATA] FROM TABLE1 WHERE CRITERIA1 IS TRUE UNION SELECT Column1, Column2, (SUBQUERY2) [SOMEDIFFERENTDATA] FROM TABLE1 WHERE CRITERIA2 IS TRUE AND CRITERIA1 IS FALSE
-
Howsabout... SELECT Column1, Column2, (SUBQUERY1) [SOMEDATA] FROM TABLE1 WHERE CRITERIA1 IS TRUE UNION SELECT Column1, Column2, (SUBQUERY2) [SOMEDIFFERENTDATA] FROM TABLE1 WHERE CRITERIA2 IS TRUE AND CRITERIA1 IS FALSE
Brilliant! I can't believe I didn't think of that. :laugh:
The difficult we do right away... ...the impossible takes slightly longer.
