CODEWITHSUNDEEP
mysqlsqlselect
stdcerr
stdcerr

Reputation: 15598

select data from two independent tables

I have two tables (MAILS & customers) in my MySQL db both having a column email storing email addresses.

Now I'd like to select every row from these two tables where emails.email!=customers.email.

When I do a simple SELECT * FROM emails, customers WHERE emails.email != customers.email, I get each emails.email listed with a data from the customers table.

How do I do that? Can I use DISTINCT but apply it only to the data coming from the customers table?

Upvotes: 1

Views: 1702

Answers (2)

Tahbaza
Tahbaza

Reputation: 9548

If you're wanting a unique list of email addresses found in either the emails table or the customers table where there is not a match between them on the email field this will do the trick:

SELECT DISTINCT COALESCE(e.Email, c.Email) as Email
FROM Emails e FULL OUTER JOIN Customers c ON e.Email = c.Email
WHERE e.Email IS NULL OR c.Email IS NULL

Coincidentally, this type of circumstance is one of the very few situations in which I have ever found the FULL OUTER JOIN to be of particular use...

Upvotes: 2

Jonathan Leffler
Jonathan Leffler

Reputation: 753505

A 'not-equals' join is very seldom helpful unless there is in fact another column that can be joined for equality. So, if you have a PersonID column in each of the two tables, then you can do:

SELECT m.*, c.*
  FROM mails AS m
  JOIN customers AS c ON c.PersonID = m.PersonID
 WHERE c.email != m.email

But without that extra condition, you have what is close to a Cartesian Product of the two tables, where almost every row in one table is matched with almost every row in the other (because of the not equals condition). Without the not equals condition, the result set would be only slightly larger.

Upvotes: 2

Related Questions