CODEWITHSUNDEEP
sql-servert-sql
Thangadurai.B
Thangadurai.B

Reputation: 561

How to simplify following SQL query?

How to simplify 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 any one sort this problem, thanks in Advance

Upvotes: 4

Views: 123

Answers (2)

Taylor Buchanan
Taylor Buchanan

Reputation: 4756

Here is another alternative using CROSS APPLY and 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 is only hit once. It uses NOT EXISTS to improve performance in the query plan by using a Left Anti Semi Join.

enter image description here

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82474

Here is one way to do it:

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

Upvotes: 2

Related Questions