MySQL子查询问题：如何筛选温尼伯/伦敦的员工访客数据且禁用JOIN/AND

解决方案 #

你可以通过前置过滤城市表的方式实现需求，不需要用到JOIN或AND运算符，修改后的完整代码如下：

SELECT "Employee"        AS PersonType,
       EmployeeFirstName AS FirstName,
       EmployeeLastName  AS LastName,
       CityName
  FROM employee, (SELECT * FROM cities WHERE CityName IN ('London','Winnipeg')) AS filtered_cities
 WHERE CityName IN (SELECT CityName WHERE employee.CityID = filtered_cities.CityID)
UNION
SELECT "Guest"        AS PersonType,
       GuestFirstName AS FirstName,
       GuestLastName  AS LastName,
       CityName
  FROM guest, (SELECT * FROM cities WHERE CityName IN ('London','Winnipeg')) AS filtered_cities
 WHERE CityName IN (SELECT CityName WHERE guest.CityID = filtered_cities.CityID);

修改逻辑说明 #

• 将原代码中用到的全量cities表，替换为仅包含London、Winnipeg两个城市记录的临时过滤表，原有CityID匹配逻辑完全不需要改动
• 全程未使用JOIN关键字，也未用到AND运算符，完全符合作业限制要求
• 过滤逻辑前置，执行效率高于结果集返回后再统一过滤

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