Hello guys!
I need your help.
I have tow table in MYSQL:
tbl_login 5.739 rows, alias A;
tbl_mailing_list 3.736 rows, alias B
The field to be related tbl_login (A) and tbl_mailing_list (B) is eemail
.
I need to know any emails of the table B are not present in table A and try this query:
SELECT
A.email,
B.email
FROM
tbl_mailing_list A
JOIN tbl_login B ON A.email = B.email
2690 rows in set
but these are the lines that have emails matching between the two tables…
Thank you for the suggestion.
r937
December 1, 2012, 9:13pm
2
“rows in one table not present in another” = LEFT OUTER JOIN with IS NULL test
SELECT B.email
FROM tbl_login B
LEFT OUTER
JOIN tbl_mailing_list A
ON A.email = B.email
WHERE A.email IS NULL
in order to prevent these situations from happening, you need to learn about foreign keys
thank you for your help.
I tried your suggestion, but in the output I have all email present in the tbl_login and not present in tbl_mailing_list.
I need the opposite:
all email present in the tbl_mailing_list and not present in tbl_login.
r937
December 1, 2012, 11:04pm
4
oh shoot, i did it the wrong way around, sorry
okay, just reverse the tables in the LEFT OUTER JOIN
Ok , thank you.
This is the new output:
SELECT B.email
FROM tbl_mailing_list B
LEFT OUTER
JOIN tbl_login A
ON A.email = B.email
WHERE A.email IS NULL
Time: 0.031ms
1039 rows in set
I try this other query and I have the same output.
What is the difference?
What is the most performant?
SELECT
email
FROM
tbl_mailing_list
WHERE
email NOT IN (SELECT email FROM tbl_login);
Time: 0.031ms
1039 rows in set
r937
December 2, 2012, 7:14pm
6
no difference in output
interesting question
sometimes NOT IN subqueries are slower
this time it appears they performed the same