我正试图根据下表获得等待服务和空闲代理的客户列表：
Customers Table
|Customers_ID||Customer Name|
|1 ||John |
|2 ||Sam |
|3 ||Kuji |
Agents Table
|AGENT_ID||AGENT Name|
|99 ||Kelly |
|98 ||Raji |
|97 ||Mertle |
Service Table (Customers being served by Agents)
|QUE_ID||AGENT Name||Customer|
|1001 ||Kelly ||Kuji |
|1002 ||Raji ||Sam |
SELECT Customer.custname
FROM Customer LEFT JOIN Service ON ( Customer.custname = Service.custname)
UNION
SELECT Agent.agentname
FROM Agent RIGHT JOIN Service ON ( Agent.agentname = Service.agentname)
WHERE
(Agent.agentname = service.agentname) IS NULL
GROUP BY Customer.custname";
这将返回客户表中的所有值，而不是没有代理的客户。如何排除已匹配的记录？
您可以使用NOT EXISTS检查尚未在Service表中的代理和客户：
SELECT custname
FROM Customer c
WHERE NOT EXISTS(
SELECT 1
FROM Service s
WHERE s.custname = c.custname
)
UNION ALL
SELECT agentname
FROM Agent a
WHERE NOT EXISTS(
SELECT 1
FROM Service s
WHERE s.agentname = a.agentname
)
看起来您正在尝试执行一个完整的外部联接。虽然MySQL没有FULL OUTER JOIN关键字，但您可以通过两个左联接的并集来模拟它。下面查询中的左侧联接给出了空闲客户和代理的数量。关键部件是WHERE ... IS NULL条件，它将匹配尚未分配的客户或代理。
SELECT c.custname
FROM Customer c LEFT JOIN Service s
ON c.custname = s.custname
WHERE s.custname IS NULL
UNION ALL
SELECT a.agentname
FROM Agent a LEFT JOIN Service s
ON a.agentname = s.agentname
WHERE s.agentname IS NULL
LEFT或RIGHT JOIN将确保返回所有没有或某些列具有NULL值的记录（在最终表中）。
您可以根据以下内容使用NOT EXISTS（这将减少记录的数量，而不是全部）：
SELECT custname
FROM Customer
WHERE NOT EXISTS (SELECT custname
FROM Service
WHERE Customer.custname = Service.custname)
UNION ALL
SELECT agentname
FROM Agent
WHERE NOT EXISTS(SELECT agentname
FROM Service s
WHERE s.agentname = a.agentname)