Pete Freitag Pete Freitag

SQL: Distinct values from two tables

databases

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...


Like this? Follow me ↯

SQL: Distinct values from two tables was first published on May 16, 2005.

If you like reading about union, sql, or duplicates then you might also like:

Comments

can u tell me the query in sql server 2000 to get sixth maximum salary
by anurag on 12/25/2005 at 5:33:35 AM UTC
How Insert District Code, Sub-Division Code and Sub-Division name eg 1 1 aaa 2 1 bbb 1 2 ccc
by Kernold on 01/21/2006 at 4:55:18 AM UTC
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
by Prashanth on 07/28/2006 at 3:32:50 AM UTC
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)
by Pallavi on 03/04/2009 at 9:17:48 PM UTC
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
by Dharmendra Jaiswal on 03/13/2009 at 4:36:28 AM UTC
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
by Dharmendra Jaiswal on 03/13/2009 at 4:36:44 AM UTC
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
by Dharmendra Jaiswal on 03/13/2009 at 4:41:17 AM UTC
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?
by Mahmud on 05/31/2009 at 9:44:00 PM UTC
SELECT * FROM <TABLENAME> T1 WHERE n=
(SELECT COUNT(DISTINCT <COLUMNNAME>) FROM <TABLENAME> T2 WHERE T1.<COLUMNNAME> <=T2.<COLUMNNAME>)
by sabari on 10/24/2011 at 1:38:47 AM UTC