如何筛选Table2中不在OrgId=25的Table1中的Typen？SQL查询求助

需求说明 #

获取Table2中所有未出现在**Table1（且Table1的orgId=25）**中的typen数据。

现有数据表 #

Table1数据 #

+----+---------------+-------+
| ID | vacancyTypeID | orgId |
+----+---------------+-------+
|  1 |             4 |    25 |
|  2 |             2 |    25 |
|  3 |             3 |    25 |
|  4 |             4 |    24 |
|  5 |             1 |    22 |
+----+---------------+-------+

Table2数据 #

+------------+----+
| typen      | id |
+------------+----+
| teacher    |  1 |
| teacher 2  |  2 |
| teacher 3  |  3 |
| teacher 4 |  4 |
| teacher 5  |  5 |
+------------+----+

原SQL问题分析 #

原SQL存在两个核心问题：

1. 语法错误：WHERE子句中table1.vacancyTypeID is null与table1.orgId = 25之间缺少逻辑运算符，导致语句无法正常执行
2. 条件位置错误：orgId=25应该放在左连接的ON条件中，若放在WHERE子句里，会直接过滤掉左连接后table1字段为NULL的行，完全偏离需求逻辑

修正后的SQL语句 #

方法一：左连接+ON条件过滤 #

SELECT table2.*
FROM table2
LEFT JOIN table1 ON table1.vacancyTypeID = table2.id AND table1.orgId = 25
WHERE table1.vacancyTypeID IS NULL;

方法二：使用NOT EXISTS（逻辑更直观） #

SELECT table2.*
FROM table2
WHERE NOT EXISTS (
    SELECT 1
    FROM table1
    WHERE table1.vacancyTypeID = table2.id AND table1.orgId = 25
);

期望结果集 #

+------------+----+
| typen      | id |
+------------+----+
| teacher    |  1 |
| teacher 5  |  5 |
+------------+----+

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