MySQL中筛选table2中name未匹配table1 hostname的行

嘿，你的查询语句有几个明显的问题，我来帮你修正，同时给你几种符合需求的写法：

首先看你原来的SQL：

SELECT * FROM table2.name a INNER JOIN table1.hostname b where a.name NOT LIKE CONCAT(b.hostname, '%');

这里有几个关键错误：

• 语法错误：FROM table2.name a 写法错误，你应该给表取别名，而不是字段，正确的形式是FROM table2 a；
• JOIN逻辑错误：INNER JOIN只会返回两张表匹配上的行，但你要的是table2中没有匹配table1 hostname的行，所以INNER JOIN完全不适用；
• 匹配逻辑偏差：你用了NOT LIKE CONCAT(b.hostname, '%')做后缀模糊匹配，但从你的预期结果来看，你需要的是精确匹配（比如table2的abc3.com完全不在table1的hostname列表里），如果没有特殊模糊匹配需求，不需要用LIKE。

接下来给你几种正确的实现方式，都能得到你想要的结果：

方法1：使用NOT EXISTS（推荐，性能最优） #

这种方式逻辑清晰，在大数据量下性能更好，只要table1的hostname字段有索引，查询速度会很快：

SELECT 
    id, 
    name, 
    created_dt AS timestamp_val
FROM table2 t2
WHERE NOT EXISTS (
    SELECT 1 
    FROM table1 t1 
    WHERE t1.hostname = t2.name
);

方法2：使用LEFT JOIN + IS NULL #

这是日常开发中很常用的写法，通过左连接后筛选出未匹配到的行：

SELECT 
    t2.id, 
    t2.name, 
    t2.created_dt AS timestamp_val
FROM table2 t2
LEFT JOIN table1 t1 ON t1.hostname = t2.name
WHERE t1.a_id IS NULL;

方法3：使用NOT IN（注意NULL值问题） #

如果table1的hostname字段不会出现NULL值，这个写法也可以，但如果存在NULL，NOT IN会返回空结果，所以要加WHERE hostname IS NOT NULL过滤：

SELECT 
    id, 
    name, 
    created_dt AS timestamp_val
FROM table2
WHERE name NOT IN (
    SELECT hostname 
    FROM table1 
    WHERE hostname IS NOT NULL
);

这三种方法都会返回你预期的结果：

id name timestamp_val
4 abc3.com 2016-11-07 10:03:32
5 abc4.com 2017-01-06 10:03:32
6 abc5.com 2015-01-06 10:03:32

另外提醒一下：尽量不要用SELECT *，而是明确指定需要的字段，这样既清晰又能避免返回不必要的字段。

内容的提问来源于stack exchange，提问作者meallhour