SQL: Distinct values from two tables

May 16, 2005
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...



Related Entries

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

Comments

can u tell me the query in sql server 2000 to get sixth maximum salary
How Insert District Code, Sub-Division Code and Sub-Division name eg 1 1 aaa 2 1 bbb 1 2 ccc
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
Thanks - you came to the top of the list in Google for "SQL DISTINCT UNION" & answered my question.
Thanks for the tip!
THANK YOU!!! I have been searching this for quite a while.
thanks for the post ...yep this answer tops in google
Thanks a lot, I didn't even think of using Union, it was exactly what I was looking for! :)
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)
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
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
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
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?
This posting might be old. But it made my day!
Thanks!
SELECT * FROM <TABLENAME> T1 WHERE n=
(SELECT COUNT(DISTINCT <COLUMNNAME>) FROM <TABLENAME> T2 WHERE T1.<COLUMNNAME> <=T2.<COLUMNNAME>)

Post a Comment




  



Spell Checker by Foundeo

Recent Entries



foundeo


did you hack my cf?