我有：
_sms_users_
id
_join_smsuser_campaigns_
sms_user_id
我想提取在join_sms user_campaigns表中没有记录的sms_用户（通过join_smsuser_campaigns.sms_user_I d=sms_users.id关系）
我有sql：
select * from sms_users where id not in (select sms_user_id from join_smsuser_campaigns);
编辑：
以下是解释选择结果：
mysql> explain select u.* from sms_users u left join join_smsuser_campaigns c on u.id = c.sms_user_id where c.sms_user_id is null;
+----+-------------+-------+-------+---------------+-------------------------------------------------------------+---------+------+-------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------------------------------------------------------+---------+------+-------+--------------------------------------+
| 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 42303 | |
| 1 | SIMPLE | c | index | NULL | index_join_smsuser_campaigns_on_campaign_id_and_sms_user_id | 8 | NULL | 30722 | Using where; Using index; Not exists |
+----+-------------+-------+-------+---------------+-------------------------------------------------------------+---------+------+-------+--------------------------------------+
mysql> describe sms_users;
+------------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
mysql> describe join_smsuser_campaigns;
+---------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------+-------+
| sms_user_id | int(11) | NO | | NULL | |
看起来问题是我没有加入的sms用户id的索引？
这大约需要5分钟，我看到有一个记录。有没有更有效的方法通过连接来实现这一点？我的sql技能相当基础。
最佳答案
当IN子句中有许多项时，查询可能会变慢。使用left join代替
select u.*
from sms_users u
left join join_smsuser_campaigns c on u.id = c.sms_user_id
where c.sms_user_id is null
见this great join explanation
关于mysql - 更有效的sql查找不在连接表中的记录？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20500019/