Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 12 Jun 2003 @ 15:04:31 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   What's up with NOT IN?
 
From:   Taylor, Peter

Hi All,

I am having some trouble with results from the following query.

SELECT COUNT(*)
FROM ETL_TEMP.XREF_VENDOR_TMP TP
WHERE TP.VENDOR_ID
NOT IN (select DISTINCT VENDOR_ID FROM ETL_TEMP.XREF_VENDOR);

this query returns a result of 0 where it should be showing that there are 3993 values of VENDOR_ID in the XREF_VENDOR_TMP that are not in the XREF_VENDOR table, However a left join between these tables does return the correct value, does anyone have an ideas as to what I have done wrong here?

I have provided the set of queries and there results along with the DDL for the tables below.

Thanks Peter.


------------------------------------------------------

SELECT COUNT(*)
FROM ETL_TEMP.XREF_VENDOR_TMP

returns: 231226

SELECT COUNT(*)
FROM ETL_TEMP.XREF_VENDOR_TMP TP
WHERE TP.VENDOR_ID
IN (SELECT VENDOR_ID FROM ETL_TEMP.XREF_VENDOR);

returns: 227233

SELECT COUNT(*)
FROM ETL_TEMP.XREF_VENDOR_TMP TP
WHERE TP.VENDOR_ID
NOT IN (SELECT VENDOR_ID FROM ETL_TEMP.XREF_VENDOR);

returns: 0
this value should be: 3993
--------------------------

however a left join on the same table set i.e.

SELECT COUNT(*) FROM ETL_TEMP.XREF_VENDOR_TMP TP
LEFT JOIN ETL_TEMP.XREF_VENDOR F ON TP.VENDOR_ID = F.VENDOR_ID
WHERE F.VENDOR_ID IS NULL

returns:  3993

As you can see in the in the DDL below the data types for the VENDOR_ID columns are the same and they are both the unique primary keys to their respective tables and there are no null values in either column. (Just for laughs I have run the same set of queries using a select distinct for the vendor_id with the same results)

CREATE SET TABLE ETL_TEMP.XREF_VENDOR ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      VENDOR_ID VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX ( VENDOR_ID );

CREATE SET TABLE ETL_TEMP.XREF_VENDOR_TMP ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      VENDOR_ID VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL)
UNIQUE PRIMARY INDEX ( VENDOR_ID );


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023