# SQL, Union queries, and removing "duplicates"

**URL:** <https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748>\
**Category:** Factual Questions\
**Created:** [January 16, 2008, 3:49pm UTC](https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748 "2008-01-16T15:49:54Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![iwakura43](https://avatars.discourse-cdn.com/v4/letter/i/9de0a6/32.png) [@iwakura43](https://boards.straightdope.com/u/iwakura43)\
**Post date:** [January 16, 2008, 3:49pm UTC](https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748/1 "2008-01-16T15:49:54Z")

</div>

I’m having a bit of difficulty using the union query exactly as I would like.

Say I have two tables, one containing my friends’ favorite liquors, and one with their favorite flowers. I want to combine the tables to make “gifts”: I prefer to buy flowers, but if there is no favorite flower in the table, I’ll buy their favorite liquor.

For example

Inputs:

```auto

LIQUORS TABLE
FRIEND LIQUOR
Alice Tequila
Bob Whiskey

FLOWERS TABLE
FRIEND FLOWER
Alice Roses
Cheryl Daisies

```

Then I want to do a query which displays

```auto

GIFTS TABLE
FRIEND GIFT
Alice Roses
Bob Whiskey
Cherly Daisies

```

I’ve gotten this to work before, but maybe I had primary keys set… how do I avoid the duplicating the Friend field?

---

<div class="post-metadata">

**Author:** ![redtail23](https://avatars.discourse-cdn.com/v4/letter/r/3e96dc/32.png) [@redtail23](https://boards.straightdope.com/u/redtail23)\
**Post date:** [January 16, 2008, 4:06pm UTC](https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748/2 "2008-01-16T16:06:15Z")

</div>

You didn’t say what kind of database you’re using, which makes giving suggestions difficult.

I’d say your best bet is to select the appropriate entries in the queries before you merge the results. The syntax below may or may not work, depending on your database.  
SELECT FRIEND, FLOWER as Gift FROM FLOWERS

UNION

SELECT FRIEND, LIQUOR as Gift FROM LIQUORS LEFT OUTER JOIN FLOWERS  
ON FLOWERS.FRIEND = LIQUORS.FRIEND  
WHERE FLOWERS.FRIEND IS NULL

---

<div class="post-metadata">

**Author:** ![wolfman](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@wolfman](https://boards.straightdope.com/u/wolfman)\
**Post date:** [January 16, 2008, 4:42pm UTC](https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748/3 "2008-01-16T16:42:35Z")

</div>

It’s possible you could do a NVL to a subquery(by the way I think in Oracle sqlplus).

Something like:

Select friend, NVL(Flower,(select Liquor from liquor B where A.friend = B.friend) )from Flower A;

But I don’t have access to a DB right now to test it. I know I’ve tried to do things like that, But I can’t remember if I ever got it to work. 🙂 I’ve tried a lot of wierd things.

---

<div class="post-metadata">

**Author:** ![Small\_Clanger](https://avatars.discourse-cdn.com/v4/letter/s/9fc348/32.png) [@Small\_Clanger](https://boards.straightdope.com/u/Small_Clanger)\
**Post date:** [January 16, 2008, 4:44pm UTC](https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748/4 "2008-01-16T16:44:23Z")

</div>

I think you could use a subquery of

```auto

WHERE NOT EXISTS <select friend by flower query here>

```

to pull out flowers in preference to booze.

I’m supposed to be writing real code at work, maybe back later if I get time.

---

<div class="post-metadata">

**Author:** ![wolfman](https://avatars.discourse-cdn.com/v4/letter/w/a8b319/32.png) [@wolfman](https://boards.straightdope.com/u/wolfman)\
**Post date:** [January 16, 2008, 4:48pm UTC](https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748/5 "2008-01-16T16:48:27Z")

</div>

Edit timeout before comepltion, trying again.  
It’s possible you could do a NVL to a subquery(by the way I think in Oracle sqlplus).

Something like:

Select friend, NVL(Flower,(select Liquor from liquor B where A.friend = B.friend) )from Flower A;

Union  
select friend, liquor from liquor where friend NOT IN (select friend from flower);

But I don’t have access to a DB right now to test it. I know I’ve tried to do things like that, But I can’t remember if I ever got it to work. 🙂 I’ve tried a lot of wierd things.

---

<div class="post-metadata">

**Author:** ![rainwalker78](https://avatars.discourse-cdn.com/v4/letter/r/eb9ed0/32.png) [@rainwalker78](https://boards.straightdope.com/u/rainwalker78)\
**Post date:** [January 16, 2008, 5:20pm UTC](https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748/6 "2008-01-16T17:20:13Z")

</div>

[QUOTE=iwakura43]  
I’m having a bit of difficulty using the union query exactly as I would like.  
[/QUOTE]

This returned exactly what you wanted in SQL Server 2005:

SELECT COALESCE(f.friend, l.friend) AS ‘Friend’, COALESCE(f.flower, l.liquor) AS ‘Gift’  
FROM #tmpFlowers F FULL JOIN #tmpLiquors l ON f.[Friend] = l.[Friend]

What a full join does is give you everything from both tables.

SELECT \* FROM #tmpFlowers F FULL JOIN #tmpLiquors l ON f.[Friend] = l.[Friend]

Friend Flower Friend Liquor  
Cheryl Daisies NULL NULL  
Alice Roses Alice Tequila  
NULL NULL Bob Whiskey

And the COALESCE gives you the first non null values from the list provided in the parenthesis.

Of course, there about 10 different ways to do this. If this was a hypothetical example for something you need to do in production, you may want to look at a few different ways, compare the execution plan and performance. If you do it this way, be careful about the order you put things in in the COALESCE - it is important.

Sorry about the edit - I wasn’t done typing and I accidentally posted

---

<div class="post-metadata">

**Author:** ![Rysto](https://avatars.discourse-cdn.com/v4/letter/r/ecccb3/32.png) [@Rysto](https://boards.straightdope.com/u/Rysto)\
**Post date:** [January 16, 2008, 5:20pm UTC](https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748/7 "2008-01-16T17:20:15Z")

</div>

[QUOTE=redtail23]  
SELECT FRIEND, FLOWER as Gift FROM FLOWERS

UNION

SELECT FRIEND, LIQUOR as Gift FROM LIQUORS LEFT OUTER JOIN FLOWERS  
ON FLOWERS.FRIEND = LIQUORS.FRIEND  
WHERE FLOWERS.FRIEND IS NULL  
[/QUOTE]

Yeah, this should work(it does on Postgres).

---

<div class="post-metadata">

**Author:** ![Small\_Clanger](https://avatars.discourse-cdn.com/v4/letter/s/9fc348/32.png) [@Small\_Clanger](https://boards.straightdope.com/u/Small_Clanger)\
**Post date:** [January 16, 2008, 5:28pm UTC](https://boards.straightdope.com/t/sql-union-queries-and-removing-duplicates/433748/8 "2008-01-16T17:28:14Z")

</div>

My data

```auto

SQL> select * from flower;

CHUM FLOWER
---------- --------------------
Bob Red Roses
Alice Pansies
Edna Peones

SQL> select * from booze;

CHUM TIPPLE
---------- --------------------
Bob Gordons
Alice Laphraig
Charlie Absinthe
Dave Beer
Edna Cinzano
Fran Vodka Martini

```

Union query

```auto

select chum, tipple
from booze
where chum not in (select chum from flower)
union
select chum, flower
from flower

```

Result

```auto

CHUM TIPPLE
---------- --------------------
Alice Pansies
Bob Red Roses
Charlie Absinthe
Dave Beer
Edna Peones
Fran Vodka Martini

```

If you’re worried about the column headings alias ‘TIPPLE’ to ‘GIFT’