如何使用SQL查询找出员工表中预期值集合内的缺失行

Alright, let's solve this problem of finding the missing EMP_NAME values (C and E) from your employee table. There are a couple of reliable, easy-to-understand approaches depending on your SQL dialect:

Method 1: Use a CTE + LEFT JOIN #

First, we'll create a list of all expected EMP_NAME values using a Common Table Expression (CTE), then compare it against your actual employee table to spot gaps.

WITH expected_employees AS (
    SELECT 'A' AS EMP_NAME UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'D' UNION ALL
    SELECT 'E'
)
SELECT ee.EMP_NAME AS missing_emp_name
FROM expected_employees ee
LEFT JOIN employee_table et 
    ON ee.EMP_NAME = et.EMP_NAME
WHERE et.EMP_NAME IS NULL;

How this works: #

• The CTE expected_employees generates the full set of names we should have (A, B, C, D, E).
• A LEFT JOIN keeps all rows from the expected list, even if there's no match in the actual employee table.
• When there's no match, et.EMP_NAME will be NULL — we filter for these rows to get our missing records.

Method 2: Use NOT EXISTS Subquery #

If you prefer a more concise approach, a NOT EXISTS check directly verifies whether each expected name exists in the table.

SELECT emp_name AS missing_emp_name
FROM (
    SELECT 'A' AS EMP_NAME UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C' UNION ALL
    SELECT 'D' UNION ALL
    SELECT 'E'
) expected_names
WHERE NOT EXISTS (
    SELECT 1
    FROM employee_table et
    WHERE et.EMP_NAME = expected_names.EMP_NAME
);

How this works: #

• The inner subquery creates our expected name list.
• NOT EXISTS checks if there's no matching row in employee_table for each expected name. If no match is found, that name is returned as missing.

Simplified Syntax for Modern Databases #

If you're using PostgreSQL, MySQL 8.0+, or SQL Server, you can use the VALUES clause to shorten the expected list creation:

-- PostgreSQL/MySQL 8.0+/SQL Server example
SELECT ee.EMP_NAME AS missing_emp_name
FROM (VALUES ('A'), ('B'), ('C'), ('D'), ('E')) AS ee(EMP_NAME)
LEFT JOIN employee_table et 
    ON ee.EMP_NAME = et.EMP_NAME
WHERE et.EMP_NAME IS NULL;

Quick Notes #

• Case Sensitivity: If your database is case-sensitive (like PostgreSQL by default), make sure the expected names match the case of values in employee_table — or use LOWER(ee.EMP_NAME) = LOWER(et.EMP_NAME) to avoid mismatches.
• Scalability: If your expected name list is long, store these values in a dedicated reference table or temporary table instead of writing multiple UNION ALL lines. This makes your query easier to maintain.

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