如何查询员工表全量数据并标记跨表存在状态、查找无匹配数据

Hey there! Let's break down your SQL questions one by one—these are super common scenarios, so I’ve got you covered.

1. 查询全量员工数据并标记是否存在于table_2 #

To get every single record from the employee table and mark whether each employee exists in table_2, you’ll want to use a LEFT JOIN (this ensures we keep all employee records, even if there’s no match in table_2) paired with a CASE statement to set the status.

Assuming you’re joining on a common field like employee_id, here’s the query:

SELECT 
    e.*,
    CASE 
        WHEN t2.employee_id IS NOT NULL THEN 'checked'
        ELSE 'unchecked'
    END AS check_status
FROM employee e
LEFT JOIN table_2 t2 ON e.employee_id = t2.employee_id;

Quick breakdown:

• The LEFT JOIN pulls all rows from employee, and matches rows from table_2 where the employee_id matches. If there’s no match, all table_2 columns will be NULL.
• The CASE statement checks if the table_2.employee_id is not null (meaning a match exists) to set the status to "checked", otherwise "unchecked".

2. 查找某表中不存在于另一表的数据 #

There are a few reliable ways to do this—let’s go through the most common ones, with examples using employee and table_1 as references:

Method 1: LEFT JOIN + IS NULL #

This is one of the most intuitive approaches:

SELECT e.*
FROM employee e
LEFT JOIN table_1 t1 ON e.employee_id = t1.employee_id
WHERE t1.employee_id IS NULL;

How it works: We left join the two tables, then filter for rows where the table_1 side has no match (indicated by NULL in the joined field).

Method 2: NOT EXISTS #

This is often efficient, especially if your join fields have indexes:

SELECT e.*
FROM employee e
WHERE NOT EXISTS (
    SELECT 1 -- No need to select actual columns, 1 is faster
    FROM table_1 t1
    WHERE t1.employee_id = e.employee_id
);

The subquery checks if there’s a matching record in table_1 for each employee. If no match exists, the NOT EXISTS condition is true, and we keep the employee record.

Method 3: NOT IN (with a caveat!) #

NOT IN can work, but you have to watch out for NULL values in the subquery results—if there’s any NULL, the entire NOT IN will return no rows. So always add a filter to exclude NULLs:

SELECT e.*
FROM employee e
WHERE e.employee_id NOT IN (
    SELECT t1.employee_id
    FROM table_1 t1
    WHERE t1.employee_id IS NOT NULL -- Critical to avoid NULL-related issues
);

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