Sql Not In Is Slowing query down to nothing? What could it be?

Transact-SQL
1

I have this query here...

SELECT DISTINCT Coalesce(NULLIF(FullDataTable.workgrp,''),'UNKNOWN') as value, Coalesce(NULLIF(AvaDepartmentLookup.departmenttitle,''),'UNKNOWN') as title
FROM FullDataTable LEFT OUTER JOIN AvaDepartmentLookup ON FullDataTable.workgrp = AvaDepartmentLookup.departmentvalue
Where (FullDataTable.workgrp not in (select AvaDepartment_Exclusion.departmentvalue from AvaDepartment_Exclusion))
order by title

If I take the where clause out, the query runs instantly. The moment I place the where clause back in, it runs forever???

The AVADepartment_Exclusion.department value table is just a simple lookup table with and 'id' column that auto populated and a 'departmentvalue' column which holds simple string values that would match workgrp?

Am I'm overlooking something.

Also, workgrp is indexed.

Thanks for any insite

2

Try creating an index on AvaDepartment_Exclusion.departmentvalue and change where statement to:

 Where not exists (select 1 from AvaDepartment_Exclusion where AvaDepartment_Exclusion.departmentvalue=FullDataTable.workgrp)
1 Like
3

AWESOME!!!

That seemed to have fixed the issue.

Can you explain a little how your where clause differed from mine (yours worked. LOL). I'm still learning alot with sql in itself.

Thanks for everything thus far though!!!

4

Another option would be:

SELECT DISTINCT
        Coalesce(NullIf(FullDataTable.workgrp, ''), 'UNKNOWN') value
      , Coalesce(NullIf(AvaDepartmentLookup.departmenttitle, ''), 'UNKNOWN') title
FROM
    FullDataTable
LEFT OUTER JOIN 
    AvaDepartmentLookup ON FullDataTable.workgrp = AvaDepartmentLookup.departmentvalue
LEFT OUTER JOIN 
    AvaDepartment_Exclusion ON FullDataTable.workgrp = AvaDepartment_Exclusion.departmentvalue
WHERE
    AvaDepartment_Exclusion.departmentvalue IS NULL
ORDER BY
    Coalesce(NullIf(AvaDepartmentLookup.departmenttitle, ''), 'UNKNOWN');
5

I'll take a look at this solution also. Learning a lot from these forums.

Thx!