Subquery SQL

Database
Highlighted
Enthusiast

Subquery SQL

Hi Team,

 

I have a question on subquery

 

I just want to know the records which doesnot exists in the table  ie fac 2 while looking up values in fac1.... If I hardcode one record ie 'AAIXERWY'  it works ( 'AAIXERWY'   value does not exists in fac 2 but exists in fac 1 hence I got the output) but if I remove the filter  --and DEBT_I = 'AAIXERWY'  then I do not get any records.  Not sure why

note: Table have nulls too.

 

select distinct DEBT_I

  from UDRBSCMS.Combine_CMS_Facility_Security_AB fac1

--left  join UDRBSCMS.FAC_CMS_Facility_Security_TEMP_AB FAC2 on Fac.ACCT_I =  Fac1.ACCT_I

 where Fac1.Business_Unit = 'IBM' and Fac1.SOURCE_SYSTEM not in ('FIN','CRL') 

 

and Fac1.LGD_CLASS = 'AE'

 

--and DEBT_I = 'AAIXERWY'           

and FAC1.DEBT_I  not  in (select  distinct Fac2.DEBT_I from  UDRBSCMS.Combine_CMS_Facility_Security_AB  fac2

                                                     where Fac2.Business_Unit = 'IBM' and Fac2.SOURCE_SYSTEM not in ('FIN','CRL') 

                                                       and Fac2.LGD_CLASS = 'AE'

                                                     and Fac2.ASET_I is  not null

                                                    --and DEBT_I = 'AAIXERWY'                     

                                                     )

  order by    FAC1.DEBT_I      

 

2 REPLIES
Teradata Employee

Re: Subquery SQL

If the DEBT_I field has any nulls, then the subquery results will look something like (val1, val2, val3, ... , NULL). And then the query wants items that are NOT IN this list. But anytime you have NOT IN (NULL), then the result will be NULL (because NULL is unknown, and we cannot know if the value is not an unknown value).

 

You can try adding  'and DEBT_I IS NOT NULL' in the subquery and you should get results.

Junior Contributor

Re: Subquery SQL

There might be NULLs returned by the subquery, add AND DEBT_I IS NOT NULL.

 

NOT IN should be avoided anyway, it's not faster than NOT EXISTS, but might be much slower, try

SELECT DISTINCT DEBT_I
FROM UDRBSCMS.Combine_CMS_Facility_Security_AB AS fac1
WHERE Fac1.Business_Unit = 'IBM'
  AND Fac1.SOURCE_SYSTEM NOT IN ('FIN','CRL') 
  AND Fac1.LGD_CLASS = 'AE'
  AND NOT EXISTS 
   (
     SELECT *
     FROM  UDRBSCMS.Combine_CMS_Facility_Security_AB AS fac2
     WHERE Fac2.Business_Unit = Fac1.Business_Unit
       AND Fac2.SOURCE_SYSTEM = Fac1.SOURCE_SYSTEM
       AND Fac2.LGD_CLASS = Fac1.LGD_CLASS
       AND Fac2.DEBT_I = Fac1.DEBT_I
       AND Fac2.ASET_I IS NOT NULL
   )
ORDER BY FAC1.DEBT_I