mysql 查询在一张表不在另外一张表的记录

SQL Join子句,主要用在select语句中,把两个或多个表的行结合起来,基于这些表之间的共同字段(往往是id字段)来查询,从多个表中返回满足条件的所有行。

常见join子句类型

  常见join子句类型有INNER JOIN(同JOIN)、LEFT JOIN、RIGHT JOIN、FULL JOIN,其中第一种为内连接,后三种为外连接。

  不同的join子句类型区别如下图所示:

介绍其中4种:
1、方法一(仅适用单个字段):使用 not in ,比较容易理解,缺点是效率低
如:select A.ID from A where A.ID not in (select ID from B);
2、方法二(适用多个字段匹配):使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录。
如:select A.ID from A left join B on A.ID=B.ID where B.ID is null ;
3、方法三(适用多个字段匹配)
如:select from B where (select count(1) as num from A where A.ID = B.ID) = 0;
4、方法四(适用多个字段匹配)
如:select
from A where not exists(select 1 from B where A.ID=B.ID)
总结:
方法一:第一种not in 场景使用子查询数据量小的情况。因为我子查询只有20w的数据。但是总表有100w。所以我使用not in的话我使用的是外表的索引所以数据较快。弊端。子查询里面不能存在null字段。如果有,那么你数据就会准。这种方式的实际情况其实是和字表做hash连接。
方法二:左连接:left join 或 left outer join
(1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null)。两表进行关联。数据量为两个表的笛卡尔积。返回左表的全部数据。右边不满足条件的为null。如果左表数据大的话,这样关联数据也不小。所以速度这么慢,属于正常。
方法四:第二种not in 场景使用子查询数据量小的情况。因为我子查询只有20w的数据。但是总表有100w。所以我使用not exists的话我使用的是子表的索引。但是我外表数据太大。所以导致速度变慢。本质:对外表作loop循环,每次loop循环再对内表进行查询。

总结:

如果右表是子表,也就是说右表有多条记录匹配左表的话,那么展示的最终结果是多条记录和左表匹配。如下图展示


总结:left join 其实也就是匹配左表的过程,on条件后不应该加上左表的条件。