How to simplify the following SQL query?

Question

How to simplify the following SQL query,

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 

can anyone solve this problem, thanks in Advance

Answer 1

Here is one way to do this:

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

Answer 2

Here is another alternative using CROSS APPLYand VALUES:

SELECT A.EMPID,
       A_D.DEPT AS 'EMP1_DEPT',
       A_D.[TYPES]
FROM @EMPLOYEE1 A
CROSS APPLY (VALUES (A.DEPT1, 0), (A.DEPT2, 1)) A_D ( DEPT, [TYPES] )
WHERE NOT EXISTS (SELECT 1
                  FROM @EMPLOYEE2 B
                  CROSS APPLY (VALUES (B.DEPT1, 0), (B.DEPT2, 1)) B_D ( DEPT, [TYPES] )
                  WHERE B_D.DEPT = A_D.DEPT
                    AND B_D.[TYPES] = A_D.[TYPES]);

The advantage of this approach is that each table hits only once. It uses NOT EXISTSto improve query performance using the left anti-Semitic association.