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
I figured I'd post this because
UNION is not used very often in SQL, so its easy to forget...
- Finding Duplicates with SQL - October 6, 2004
(SELECT COUNT(DISTINCT <COLUMNNAME>) FROM <TABLENAME> T2 WHERE T1.<COLUMNNAME> <=T2.<COLUMNNAME>)
- Updating Java on ColdFusion or Lucee
- ColdFusion returning empty response with server-error: true
- Careful applying CF11u16, CF2016u8, CF2018u2
- Sessions don't work in Chrome but do in IE
- csrfVerifyToken does not invalidate the token
- The cf_sql_ is optional in cfqueryparam
- Cookie Expires / Max-Age 1969-12-31T23:59:59.000Z
- Burst Throttling on AWS API Gateway Explained