SQL: Distinct values from two tables

May 16, 2005

Today must be database day for me...

A question on my local CFUG mailing list asks how to remove duplicate values from two different tables:

I have 2 tables that store email addresses. One table is for newsletters and the other is for registration to our site. I would like to make a list of all the email addresses we have but not show duplicate addresses. How do I go about doing this?

The answer - UNION two SQL queries:

SELECT email FROM registration
UNION
SELECT email FROM newsletter

UNION will remove all the duplicates for you. If you wanted to show the duplicates as well, you would use UNION ALL.

I figured I'd post this because UNION is not used very often in SQL, so its easy to forget...

Permalink | Add Comment |

add to del.icio.us | Tags: union, sql, duplicates

Related Entries

Finding Duplicates with SQL - October 6, 2004

15 people found this page useful, what do you think?

Trackbacks

Trackback Address: 358/5ADABFE3337D8CA4BE9A12351FE51AC9

Comments

On 12/25/2005 at 7:33:35 AM EST anurag wrote:

can u tell me the query in sql server 2000 to get sixth maximum salary

On 01/21/2006 at 6:55:18 AM EST Kernold wrote:

How Insert District Code, Sub-Division Code and Sub-Division name eg

1 1 aaa 2 1 bbb 1 2 ccc

On 07/28/2006 at 5:32:50 AM EDT Prashanth wrote:

Your code is very useful but how to get two fields from one table and one field of other table using this union i mean from Table registration name, email and from table newsletter without EMAIL duplicate

On 12/21/2006 at 1:47:22 PM EST LeeK wrote:

Thanks - you came to the top of the list in Google for "SQL DISTINCT UNION" & answered my question.

On 04/03/2007 at 10:54:13 AM EDT Mike wrote:

Thanks for the tip!

On 05/14/2007 at 9:18:22 PM EDT Mitch Gaffigan wrote:

THANK YOU!!! I have been searching this for quite a while.

On 10/24/2007 at 12:52:27 AM EDT sara_23apr wrote:

thanks for the post ...yep this answer tops in google

On 09/21/2008 at 10:45:38 PM EDT Bill wrote:

Thanks a lot, I didn't even think of using Union, it was exactly what I was looking for! :)

On 03/04/2009 at 11:17:48 PM EST Pallavi wrote:

Question: can u tell me the query in sql server 2000 to get sixth maximum salary

Reply: SELECT TOP 1 * FROM (SELECT TOP 6 * FROM (SELECT EMPLOYEE.FstNAME,SALARY_DETAILS.AMOUNT FROM EMPLOYEE INNER JOIN SALARY_DETAILS ON EMPLOYEE.ID = SALARY_DETAILS.ID) AS D ORDER BY D.AMOUNT DESC) AS P ORDER BY AMOUNT

This will work.It takes date from two tables and find 6th maximum salary(amount)

On 03/13/2009 at 6:36:28 AM EDT Dharmendra Jaiswal wrote:

If you think you are capable of being an Oracle Expert Guy then answer my query;

How to spell salary column of emp table using Sql

On 03/13/2009 at 6:36:44 AM EDT Dharmendra Jaiswal wrote:

If you think you are capable of being an Oracle Expert Guy then answer my query;

How to spell salary column of emp table using Sql

On 03/13/2009 at 6:41:17 AM EDT Dharmendra Jaiswal wrote:

How to find 3rd max salary from emp table without using max() function. And also tell me how to go directly to Command prompt from the Oracle9i Sql Editor

On 05/31/2009 at 11:44:00 PM EDT Mahmud wrote:

I have 2 tables, One have 2 rows and another have 3 row. I want to see the both of two table in one table. But the desire table show SIX row. But I want to see the three rows. But How?

On 10/14/2010 at 11:56:49 AM EDT Tasha wrote:

This posting might be old. But it made my day! Thanks!

On 10/24/2011 at 3:38:47 AM EDT sabari wrote: