SQL: Distinct values from two tables
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...
add to del.icio.usRelated Entries
- Finding Duplicates with SQL - October 6, 2004
Trackbacks
Trackback Address: 358/5ADABFE3337D8CA4BE9A12351FE51AC9
Comments
On 12/25/2005 at 5:33:35 AM EST anurag wrote:
1
can u tell me the query in sql server 2000 to get sixth maximum salary
On 01/21/2006 at 4:55:18 AM EST Kernold wrote:
2
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 3:32:50 AM EDT Prashanth wrote:
3
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 11:47:22 AM EST LeeK wrote:
4
Thanks - you came to the top of the list in Google for "SQL DISTINCT UNION" & answered my question.
On 04/03/2007 at 8:54:13 AM EDT Mike wrote:
5
Thanks for the tip!
On 05/14/2007 at 7:18:22 PM EDT Mitch Gaffigan wrote:
6
THANK YOU!!! I have been searching this for quite a while.
On 10/23/2007 at 10:52:27 PM EDT sara_23apr wrote:
7
thanks for the post ...yep this answer tops in google
On 09/21/2008 at 8:45:38 PM EDT Bill wrote:
8
Thanks a lot, I didn't even think of using Union, it was exactly what I was looking for! :)
On 03/04/2009 at 9:17:48 PM EST Pallavi wrote:
9
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 4:36:28 AM EDT Dharmendra Jaiswal wrote:
10
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 4:36:44 AM EDT Dharmendra Jaiswal wrote:
11
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 4:41:17 AM EDT Dharmendra Jaiswal wrote:
12
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 9:44:00 PM EDT Mahmud wrote:
13
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?
Post a Comment
Recent Entries
- Cache Template in Request Setting Explained
- What Version of Java is ColdFusion Using?
- ColdFusion 9 Performance Brief from Adobe
- Request Filtering in IIS 7 Howto
- J2EE Session Cookies on ColdFusion / JRun
- Hands on ColdFusion Security Training
- ColdFusion 9 Solr Vulnerability - Are you at Risk?
- FCKEditor Year 2010 Bug for Firefox 3.6 with ColdFusion
1 1 aaa 2 1 bbb 1 2 ccc
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)
How to spell salary column of emp table using Sql
How to spell salary column of emp table using Sql
