mysql> select email from sh_mail_list where `group`='5';
+-------------------------+
| email                   |
+-------------------------+
| me@me.com        |
| you@you.com |
| me.test@gmail.com   |
| me@me.com        |
[[[[ if i add a new row here then AND should be used]]]]]
+-------------------------+
4 rows in set (0.00 sec)


mysql> select email from table1;
+-----------------------+
| email                 |
+-----------------------+
| me.test@gmail.com |
+-----------------------+
1 row in set (0.00 sec)



mysql> select email from table2;
+-------------------------+
| email                   |
+-------------------------+
| me.test@gmail.com   |
| you@you.com |
+-------------------------+
2 rows in set (0.00 sec)


mysql> select email from table3;
+-------------------------+
| email                   |
+-------------------------+
| me.test@gmail.com   |
| me@me.com        |
| you@you.com |
+-------------------------+
3 rows in set (0.00 sec)


mysql> select a.*,b.id,c.id,d.id from sh_mail_list a
    ->  left join table1 b on a.email=b.email
    ->  left join table2 c on a.email=c.email
    ->  left join table3 d on a.email=d.email
    ->
    -> where
    -> a.group='5' AND (
    -> b.id is null OR
    -> c.id is null OR
    -> d.id is null
    -> );
+-----+-------------------------+--------+------+--------+-----------+-------+--------+-------+------+--------+------+------+------+----------+-------+-----------+----------+---------+-------+--------------+------+------+-------+------+------+------+
| id  | email                   | email2 | type | sector | subscribe | phone | mobile | skype | fax  | street | zip  | city | land | language | title | firstname | lastname | company | group | importedfrom | flag | note | extra | id   | id   | id   |
+-----+-------------------------+--------+------+--------+-----------+-------+--------+-------+------+--------+------+------+------+----------+-------+-----------+----------+---------+-------+--------------+------+------+-------+------+------+------+
| 335 | me@me.com        | NULL   | NULL | NULL   | NULL      | NULL  | NULL   | NULL  | NULL | NULL   | NULL | NULL | NULL | NULL     | NULL  | NULL      | NULL     | NULL    | 5     |              |      |      |       | NULL | NULL |    2 |
| 336 | you@you.com | NULL   | NULL | NULL   | NULL      | NULL  | NULL   | NULL  | NULL | NULL   | NULL | NULL | NULL | NULL     | NULL  | NULL      | NULL     | NULL    | 5     |              |      |      |       | NULL |    2 |    3 |
| 338 | me@me.com        | NULL   | NULL | NULL   | NULL      | NULL  | NULL   | NULL  | NULL | NULL   | NULL | NULL | NULL | NULL     | NULL  | NULL      | NULL     | NULL    | 5     |              |      |      |       | NULL | NULL |    2 |
+-----+-------------------------+--------+------+--------+-----------+-------+--------+-------+------+--------+------+------+------+----------+-------+-----------+----------+---------+-------+--------------+------+------+-------+------+------+------+
3 rows in set (0.01 sec)

mysql>