mysql select from table 1 where no match in table 2

Question

I have two tables, both contain email address.
I need to return all rows in table 1 that do not have a matching email address in table 2.
For simplicity sake we can just say they both have two fields, name and email.

isaace answered ... waiting for timer to expire to check the box — Xi Vix, Aug 08 '17 at 20:16

score 0 · Accepted Answer · answered Aug 08 '17 at 20:13

0

select * from table1 where emailAddress not it (select email address from table2)

answered Aug 08 '17 at 20:13

isaace

3,336
1
9
22

score 0 · Answer 2 · answered Aug 08 '17 at 20:28

0

You can try LEFT JOIN with NULL in where clause. In LEFT JOIN, if table2 has no matching values, it will be represented by NULL.

SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.email = Table2.email WHERE Table2.email IS NULL

answered Aug 08 '17 at 20:28

Praveen E

926
8
14

score 0 · Answer 3 · answered Feb 14 '23 at 18:14

SELECT 
table_A.column_1, 
table_A.column_2, 
table_A.email

FROM table_A

WHERE table_A.email 
NOT IN ( SELECT table_B.email FROM table_B )

An example with several columns from Table A.

Both, Table A and Table B have a column named "email".

Matching emails from Table B will be omitted in the query results.

( This question is similar to Mysql: Select rows from a table that are not in another )

mysql select from table 1 where no match in table 2

3 Answers3

Linked