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.

Ravel

isaace answered ... waiting for timer to expire to check the box – Ravel

Answer 1

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

Petulancy

Answer 2

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

Icon

Answer 3

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 )

Bicameral