Pete FreitagSQL: Distinct values from two tables
Published on May 16, 2005
By Pete Freitag
By Pete Freitag
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...
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:
Discuss / Follow me on Twitter ↯
Tweet Follow @pfreitagComments
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
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)
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
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
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
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?
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>)
(SELECT COUNT(DISTINCT <COLUMNNAME>) FROM <TABLENAME> T2 WHERE T1.<COLUMNNAME> <=T2.<COLUMNNAME>)
by sabari on 10/24/2011 at 1:38:47 AM UTC