sql-server - 如何简化以下SQL查询？

如何简化以下SQL查询，

DECLARE @EMPLOYEE1 TABLE (EMPID INT,DEPT1 INT,DEPT2 INT)
DECLARE @EMPLOYEE2 TABLE (EMPID INT,DEPT1 INT,DEPT2 INT)

    INSERT INTO @EMPLOYEE1 VALUES
    (1,1,1),
    (2,2,2),
    (3,10,3),
    (4,4,4)
    INSERT INTO @EMPLOYEE2 VALUES
    (1,1,1),
    (2,2,2),
    (3,10,10),
    (4,10,4)

    SELECT  A.EMPID,
            A.DEPT1 EMP1_DEPT,
            0 TYPES
    FROM    @EMPLOYEE1 A
    LEFT JOIN @EMPLOYEE2 B ON A.DEPT1=B.DEPT1
    WHERE   B.DEPT1 IS NULL

    UNION ALL

    SELECT  A.EMPID,
            A.DEPT2 EMP2_DEPT,
            1 TYPES
    FROM    @EMPLOYEE1 A
    LEFT JOIN @EMPLOYEE2 B ON A.DEPT2=B.DEPT2
    WHERE   B.DEPT2 IS NULL

谁能解决这个问题，谢谢

最佳答案

这是一种实现方法：

SELECT  DISTINCT
        A.EMPID,
        CASE WHEN B.DEPT1 IS NULL THEN A.DEPT1 ELSE A.DEPT2 END As EMP1_DEPT,
        CASE WHEN B.DEPT1 IS NULL THEN 0 ELSE 1 END As TYPES
FROM    @EMPLOYEE1 A
LEFT JOIN @EMPLOYEE2 B ON A.DEPT1=B.DEPT1
LEFT JOIN @EMPLOYEE2 C ON A.DEPT2=C.DEPT2
WHERE B.DEPT1 IS NULL
OR C.DEPT2 IS NULL