An alternative for "NOT IN" in SQL SERVER

Question

I have 2 tables:

1. Deparment table (save data of Deparment):

2. AccDocSales table (save data of Order made by which Department):

Now, I want to select these Departments, which is not showing in AccDocSales table per MONTH (This mean which Department does't have DeptCode in AccDocSales table per MONTH).

Ex (for this case):

I used this query:

SELECT distinct MONTH(DocDate) as THANG, B20Dept.Code, B20Dept.Name
FROM B20Dept, B30AccDocSales S1
WHERE YEAR(S1.DocDate) = 2014 AND B20Dept.Code NOT IN
(
    SELECT S2.DeptCode 
    FROM B30AccDocSales S2, B20Dept 
    WHERE YEAR(S2.DocDate) = 2014 AND S2.DeptCode = B20Dept.Code 
    AND MONTH(S1.DocDate) = MONTH(S2.DocDate)
)
ORDER BY MONTH(DocDate)

It working but my teacher said "NOT IN" in this query is NOT acceptable. He asked me find another way to do this without "IN", "NOT IN".

PS: I find one more problem with this query. That is which month have all Department in "DeptCode" and which month have no row, they all do not show up any result when run that query.

Please help.

Answer 1

ALTERNATIVES OF NOT IN-

• Example Code –

  IF OBJECT_ID('Tempdb..#SampleTable1') IS NOT NULL DROP TABLE #SampleTable1; IF OBJECT_ID('Tempdb..#SampleTable2') IS NOT NULL DROP TABLE #SampleTable2;

  SET NOCOUNT ON;

  CREATE TABLE #SampleTable1 ( NUM INT NOT NULL ); GO

  INSERT INTO #SampleTable1 (NUM) VALUES (1),(2),(3),(4),(5) ; GO

  CREATE TABLE #SampleTable2 ( NUM INT NOT NULL ); GO

  INSERT INTO #SampleTable2 (NUM) VALUES (4),(5),(6),(7),(8) ; GO

  SELECT NUM AS [#SampleTable1] FROM #SampleTable1; GO SELECT NUM AS [#SampleTable2] FROM #SampleTable2; GO

1. NOT EXISTS

--METHOD 1 (Using NOT EXISTS)
SELECT S1.NUM AS [NOT EXISTS] 
FROM #SampleTable1 S1 
WHERE NOT EXISTS (SELECT NUM FROM #SampleTable2 S2 WHERE S2.NUM=S1.NUM);
GO

2. EXCEPT

--METHOD 2 (Using EXCEPT)
SELECT NUM AS [EXCEPT] 
FROM #SampleTable1 
EXCEPT
SELECT NUM FROM #SampleTable2;
GO

3. ANY

--METHOD 3 (Using = ANY)
SELECT S1.NUM AS [= ANY]
FROM #SampleTable1 S1
WHERE NOT (S1.NUM = ANY
(
SELECT S2.NUM
FROM #SampleTable2 S2
) );

4. OUTER APPLY

--METHOD 4 (Using OUTER APPLY and avoiding JOIN CONDITION)
SELECT S1.NUM AS [OUTER APPLY] 
FROM #SampleTable1 S1 
OUTER APPLY (
SELECT NUM FROM #SampleTable2 S2 WHERE S2.NUM=S1.NUM
) T 
WHERE T.NUM IS NULL;
GO

5. LEFT JOIN / IS NULL

--METHOD 5 (Using LEFT JOIN/IS NULL)
SELECT S1.NUM AS [LEFT JOIN] 
FROM #SampleTable1 S1 
LEFT JOIN #SampleTable2 S2 ON S1.NUM=S2.NUM
WHERE S2.NUM IS NULL;
GO

6. CORRELATED SUBQUERY

--METHOD 6 (Using CORRELATED SUBQUERY)
SELECT NUM AS [CORRELATED SUBQUERY]
FROM #SampleTable1 AS S1 
WHERE (SELECT COUNT(*) FROM #SampleTable2 S2
WHERE S2.NUM = S1.NUM) = 0;
GO

7. ALL

--METHOD 7 (Using <> ALL)
SELECT NUM AS [<> ALL]
FROM #SampleTable1
WHERE NUM <>ALL
(
SELECT NUM
FROM #SampleTable2
);
GO

8. CROSS APPLY

--METHOD 8 (CROSS APPLY)
SELECT T1.NUM AS [CROSS APPLY] 
FROM #SampleTable1 AS S1
CROSS APPLY
(
    SELECT S1.NUM
    EXCEPT
    SELECT NUM
    FROM #SampleTable2
) T1;
GO

9. FULL OUTER JOIN

--METHOD 9 (Using FULL OUTER JOIN/IS NULL)
SELECT S1.NUM AS [FULL OUTER JOIN] 
FROM #SampleTable1 S1 
FULL OUTER JOIN #SampleTable2 S2 ON S1.NUM=S2.NUM
WHERE S2.NUM IS NULL;
GO

NOT IN

--METHOD 10 (Using NOT IN)
SELECT NUM AS [NOT IN] 
FROM #SampleTable1 
WHERE NUM NOT IN
(SELECT NUM FROM #SampleTable2);
GO

For Small Number of Data, NOT EXISTS is best choice irrespective of With/Without Index,Null or Non-Null Values. In this case, LEFT JOIN/IS NULL is least efficient while comparing to the performance of other Alternatives because of its behavior to not skipping the already matched values with the right table and returning all the results and filtering them out at final steps using IS NULL filter. But For Large set of Data, Sub query method is not recommended.

Answer 2

One simple solution, most of the time faster than not in is :

Select * from A where 0 = ( select count(*) from B where A.id = B.id)

Answer 3

Try this solution:

select s.*, d.*
from B20Dept d
    cross apply (select distinct YEAR(s.DocDate) Y, MONTH(s.DocDate) THANG from B30AccDocSales s) s
    left join (
        select YEAR(s.DocDate) Y, MONTH(s.DocDate) THANG, s.DeptCode
        from B30AccDocSales s
        group by YEAR(s.DocDate), MONTH(s.DocDate), s.DeptCode) m on m.Y = s.Y and m.THANG = s.THANG and m.DeptCode = d.Code
where m.DeptCode is null 
order by s.Y, s.THANG

EDIT: In the query below, You can find a solution for the problem, that is in You PS:

declare @Year int = 2014
select s.*, d.*
from B20Dept d
    cross apply (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) s (THANG)
    left join (
        select MONTH(s.DocDate) THANG, s.DeptCode
        from B30AccDocSales s
        where YEAR(s.DocDate) = @Year
        group by MONTH(s.DocDate), s.DeptCode) m on m.THANG = s.THANG and m.DeptCode = d.Code
where m.DeptCode is null
union 
select MONTH(s.DocDate) THANG, '', ''
from B30AccDocSales s
where YEAR(s.DocDate) = @Year
group by MONTH(s.DocDate)
having COUNT(distinct s.DeptCode) = (select count(1) from B20Dept)
order by s.THANG