CODEWITHSUNDEEP
mysqljoin
Tauras
Tauras

Reputation: 3904

Return not matching rows after joining two tables

I am not sure how to ask this correctly, but here it is:

table1

id   |    email
---------------------------
1         [email protected]
---------------------------
2         [email protected]
---------------------------
3         [email protected]
---------------------------
4         [email protected]
---------------------------
5         [email protected]
---------------------------
6         [email protected]
---------------------------
7         [email protected]

table2

id   |    email_two
---------------------------
7         [email protected]
---------------------------
10        [email protected]
---------------------------
33        [email protected]
---------------------------
99        [email protected]
---------------------------
109       [email protected]
---------------------------
299       [email protected]

Question:

How to correctly join two tables, and get not matching results by email? For example, what i need to get from both tables is:

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

because other emails matching each other.

Code

SELECT email_two FROM table2 b WHERE NOT EXISTS (SELECT * FROM table1 a WHERE a.email = b.email_two

This code returns only missing ones from table2, but i cant find a correct way to return missing results from two tables in one query.

Thanks for any answers.

Upvotes: 0

Views: 542

Answers (2)

Sanjay
Sanjay

Reputation: 2008

you can use LEFT JOIN and UNION ALL to get this, below is sample query that may help you to get these records.

select t1.email as 'email'  from  t1 LEFT JOIN t2 on t1.email = t2.email
where t2.email is null
UNION ALL
select t2.email as 'email'  from  t2 LEFT JOIN t1 on t2.email = t1.email 
where t1.email is null;

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

The most suitable operation for what you want is a FULL OUTER JOIN which is unfortunately not supported in MySQL.

You can use UNION ALL instead:

SELECT email_two AS email
FROM table2 b 
WHERE NOT EXISTS (SELECT * FROM table1 a 
                  WHERE a.email = b.email_two)

UNION ALL

SELECT email 
FROM table1 a 
WHERE NOT EXISTS (SELECT * FROM table2 b 
                  WHERE a.email = b.email_two)

Upvotes: 1

Related Questions