delete records in table A not in table B
in not Table delete
2023-09-27 14:25:24 时间
转)A、B两表,找出ID字段中,存在A表,但是不存在B表的数据。A表总共13w数据,去重后大约3W条数据,B表有2W条数据,且B表的ID字段有索引。
方法一
使用 not in ,容易理解,效率低 ~执行时间为:1.395秒~
1 select distinct A.ID from A where A.ID not in (select ID from B)
方法二
使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录 ~执行时间:0.739秒~
1 select A.ID from A left join B on A.ID=B.ID where B.ID is null
图解
方法三
逻辑相对复杂,但是速度最快 ~执行时间: 0.570秒~(感觉这种方式挺好)之前A/B表位置写反了,很明显可以看出为问题所在
,在碰到问题可以分一下
1 select * from A
2 where (select count(1) as num from B where A.ID = B.ID) = 0
相关文章
- Non-matching values for modulus and p*q in RSA encryption
- Does it make sense to put antiforgerytoken in _Layout.cshtml?
- What is the !! (not not) operator in JavaScript?
- 报错:The specified datastore driver ("com.mysql.jdbc.Driver") was not found in the CLASSPATH. Please check your CLASSPATH specification, and the name of the driver.
- 关于微信小程序报错:Some selectors are not allowed in component wxss, including tag name selectors...
- 【CF840E】In a Trap 分块
- CodeForces 161D Distance in Tree
- Error: [mobx] Since strict-mode is enabled, changing observed observable values outside actions is not allowed. Please wrap the code in an `action` if this change is intended.
- C#,数值计算(Numerical Recipes in C#),大型稀疏线性系统(Sparse Linear Systems)的数据存储结构与源代码
- Lucene in action 笔记 analysis篇
- Linux有问必答:怎样解决“XXX is not in the sudoers file”错误
- ./configure时候遇到的问题 Cannot find install-sh, install.sh, or shtool in ac-aux
- 替代not in 和 in 的办法
- SAP MM - Why is the freight not included in the material cost at the time of GR?
- unknown directive “stream” in /usr/local/nginx
- And one thing may differ I found is that, it only compatible with python3,so when your default pip installed in python is 2.7 not python3 you need use "python3 -m pip install chefboost" instead
- Learning to Explore in Motion and Interaction Tasks
- UnicodeDecodeError: 'ascii' codec can't decode byte 0xa0 in position 0: ordinal not in range(128)
- 【转】Windows2012设置文件夹权限报错:failed to enumerate objects in the container.
- Mysql解决 Expression #11 of SELECT list is not in GROUP BY clause and contains nonaggregated column
- Failed to load module “canberra-gtk-module“ 或 Using GTK+ 2.x and GTK+ 3 in the same process is not
- Eclipse迅速执行:Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
- 解决MySQL报错:1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'informat
- [LeetCode] 778. Swim in Rising Water 在上升的水中游泳
- leetcode 524. Longest Word in Dictionary through Deleting 通过删除字母匹配到字典里最长单词