刘明野

SQL查询,id存在A表,不存在B表的三种写法

摘要:日常开发中经常遇到这样的问题,某一个id在A表中,但是不存在在B表中,那么我们如何查询有多少存在A表,不存在B表的数据呢,我想大部分人首先想到的肯定是not in语法,这里我分享几种除了not in之外的写法,并且效率也比not in高。

一:业务场景

有这样的两个表:用户表,会员表,其中会员表里面有用户ID标识,正常的业务场景是,用户激活的时候创建对应的会员,这样就能把会员和用户关联起来,其中关联的外建就是userId,现在我想查用户已经激活,但是还没有创建会员的用户,这就非常符合上面摘要描述的业务场景了,下面看下具体的三种写法:

二:两个表的SQL结构如下:

CREATE TABLE `au_user` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `user_name` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '用户名',
  `real_name` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '姓名',
  `password` char(64) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
  `salt` char(32) COLLATE utf8_bin DEFAULT NULL COMMENT '密码加密盐',
  `sex` bit(1) DEFAULT NULL COMMENT '性别',
  `phone` bigint(20) DEFAULT NULL COMMENT '手机号',
  `login_org_id` bigint(20) DEFAULT NULL COMMENT '最好登录组织ID',
  `creator` bigint(20) DEFAULT NULL COMMENT '创建人',
  `modifier` bigint(20) DEFAULT NULL COMMENT '修改人',
  `ts_insert` datetime DEFAULT NULL COMMENT '创建时间',
  `ts_update` datetime DEFAULT NULL COMMENT '修改时间',
  `dr` bit(1) DEFAULT NULL COMMENT '删除标志,0表示未删除,null表示已删除',
  `activate` bit(1) NOT NULL COMMENT '是否激活:默认值false:否(0),true:是(1)',
  `permission_level` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '权限级别:(超级管理员: SUPER_SYSTEM_LEVEL - 系统管理员: SYSTEM_LEVEL - 机构管理员 ORGANIZATION_LEVEL - 普通用户 GENERAL_USER_LEVEL)',
  `open_id` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '微信openId',
  `start_up` bit(1) DEFAULT b'0' COMMENT '停用/启用,0启用1停用',
  `head_portrait` bigint(20) DEFAULT NULL COMMENT '用户头像文件ID',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_name` (`user_name`,`dr`),
  UNIQUE KEY `uk_phone` (`phone`,`dr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用户';
CREATE TABLE `au_member` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `name` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '用户名',
  `real_name` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '姓名',
  `password` char(64) COLLATE utf8_bin DEFAULT NULL COMMENT '密码',
  `salt` char(64) COLLATE utf8_bin DEFAULT NULL COMMENT '密码加密盐',
  `sex` bit(1) DEFAULT NULL COMMENT '性别',
  `active` bit(1) DEFAULT NULL COMMENT '是否已激活,默认值false:否,true:是',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `creator` bigint(20) DEFAULT NULL COMMENT '创建人',
  `modifier` bigint(20) DEFAULT NULL COMMENT '修改人',
  `ts_insert` datetime DEFAULT NULL COMMENT '创建时间',
  `ts_update` datetime DEFAULT NULL COMMENT '修改时间',
  `dr` bit(1) DEFAULT NULL COMMENT '删除标志,0表示未删除,null表示已删除',
  `phone` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '手机号',
  `status` bit(1) DEFAULT NULL COMMENT '登录状态 0 未登录,1 已登录',
  `head_portrait` bigint(20) DEFAULT NULL COMMENT '会员头像文件ID',
  `open_id` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `email` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '邮箱',
  `wei_xin_code` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '微信号',
  `qq_code` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT 'QQ号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='会员表';

方法一:使用 not in ,容易理解,效率低

SELECT
    u.*
FROM
    au_user u
WHERE u.dr = 0
AND u.activate = 1
AND u.id NOT IN (SELECT m.user_id FROM au_member m WHERE m.user_id IS NOT NULL);

方法二:使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录

SELECT
    u.*
FROM
    au_user u
LEFT JOIN au_member m ON u.id = m.user_id
WHERE u.dr = 0
AND u.activate = 1
AND m.user_id IS NULL;

方法三:逻辑相对复杂,但是速度最快

SELECT
    u.*
FROM
    au_user u
WHERE u.dr = 0
AND u.activate = 1
AND (SELECT count(1) AS num FROM au_member m WHERE m.user_id = u.id) = 0;

最后鸣谢:https://www.cnblogs.com/jameshappy/p/6038706.html

转载自:https://blog.csdn.net/sxdtzhaoxinguo/article/details/80228721?utm_source=blogxgwz11

本文为作者刘明野发布,未经允许禁止转载!
446
0
2
发表留言

友情链接