How to find records are exist or not from table in mysql table

Question

I want to fetch records from first table & check that records are exist in second table or not:

tbl_user

userid      email
-------------------------
1       abc@gmail.com
2       abcd@gmail.com
3       abedd@yahoo.com
4       xyz@gmail.com
5       test@ymail.com

tbl_user_responce

id      responce_email
-------------------------
1       abc@gmail.com
2       abcd@gmail.com
3       abc@yahoo.com
4       xyz@gmail.com
5       abcd@ymail.com

UPDATE

Note: In my secord table email is stored in xml format like following:

<?xml version="1.0" encoding="UTF-8"?>
<user>
<userinfo>
<email>stevemartin148@gmail.com</email>
<status>1</status>
<description>Success</description>
</userinfo>
</user>

i want to fetch those records that are not exist in tbl_user_responce from tbl_user

So from above records i want result like

Email
-----
abedd@yahoo.com
test@ymail.com

Does anybody know how to manage that?

Thanks in advance.

possible duplicate of [MINUS oprator in MySQL?](http://stackoverflow.com/questions/8386280/minus-oprator-in-mysql) — Danny, Aug 15 '13 at 12:33
no i want to fetch email from first table & match with second table & if records not matched then display listing of unmatched records — Steve Martin, Aug 15 '13 at 12:33
I think you need to reconsider your schema. If you need to store xml, keep it, but add extra ``email`` column to ``tbl_user_responce`` table and index by it. Otherwise you will have performance issues very soon — Pavel Dubinin, Jul 06 '14 at 08:03

score 1 · Answer 1 · answered Aug 15 '13 at 12:33

How about something like

SELECT  *
FROM    tbl_user
WHERE   NOT EXISTS (
                        SELECT  1
                        FROM    tbl_user_responce
                        WHEREN  tbl_user.email = tbl_user_responce.responce_email
                    )

Or even something like

SELECT  tbl_user.*
FROM    tbl_user LEFT JOIN
        tbl_user_responce ON    tbl_user.email = tbl_user_responce.responce_email
WHERE   tbl_user_responce.responce_email IS NULL

Joachim Isaksson · Answer 2 · 2013-08-15T12:56:06.000

0

The query you're asking for won't use any indexes, but this would seem to do the job as long as there's only one email per "responce";

SELECT email 
FROM tbl_user
WHERE email NOT IN (
  SELECT SUBSTRING_INDEX(
           SUBSTRING_INDEX(responce_email,'<email>', -1),
         '</email>',1) 
  FROM tbl_user_responce;    
)

From the "I'd do an SQLfiddle if it weren't down" dept.

edited Aug 15 '13 at 12:56

answered Aug 15 '13 at 12:33

Joachim Isaksson

176,943
25
281
294

How to find records are exist or not from table in mysql table

UPDATE

2 Answers2