SQL efficient way to find lines where mobile and mail not in another table?

advertisements

I have to tables with users. Both have one mail column and one mobile column. I have to find all rows in table1 where both mail and mobile is not in table2. And mail and mobile is not empty in table1.

The object is to find users from table1 that doesn't yet exist in table2. And we use mail or mobile to identify them.

I use MySQL

table1
|mail       |mobile  |
|[email protected] |12345678|
|           |21345678|
|[email protected] |84726287|

table2
|mail       |mobile  |
|[email protected] |12345678|
|[email protected] |21888888|
|[email protected] |        |

Result
|[email protected] |12345678|
|           |21345678|


If you want to find users where there is no mail and no mobile in the other table:

SELECT t1.*
FROM dbo.Table1 t1
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Table2 t2
    WHERE t1.Mail = t2.Mail
)
AND NOT EXISTS
(
    SELECT 1
    FROM dbo.Table2 t2
    WHERE t1.Mobile = t2.Mobile
)

If both must match on the same row it's simpler:

SELECT t1.*
FROM dbo.Table1 t1
WHERE NOT EXISTS
(
    SELECT 1
    FROM dbo.Table2 t2
    WHERE t1.Mail = t2.Mail AND t1.Mobile = t2.Mobile
)