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

亦凉 2024-02-18 20:06 65阅读 0赞

**摘要**：日常开发中经常遇到这样的问题，某一个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_www.cnblogs.com_jameshappy_p_6038706.html]

[https_www.cnblogs.com_jameshappy_p_6038706.html]: https://www.cnblogs.com/jameshappy/p/6038706.html