Oracle inconsistent sql result

user_5955138 0
Viewed 102

I have three exactly same sql queries. However, one of them does not return any results. I couldn't find proper explaination for the issue.

Anyone who can 

--returns one result
   SELECT col1
   FROM table1
   WHERE col1  not IN
   (select x.hesno from (SELECT c1||c2||c3 FROM Table2 S) x) ; 

--returns no result
   SELECT col1
   FROM table1
   WHERE col1  not IN
     (SELECT c1||c2||c3 FROM table2)  ;


--returns one result
   SELECT col1
   FROM Table1
   WHERE NOT EXISTS (
     SELECT 1 FROM table2 WHERE c1||c2||c3 = col1
   );

Here is the table descriptions

Table1
Name   Null? Type          
------ ----- ------------- 
COL1          VARCHAR2(15)  

Table2
Name            Null? Type          
--------------- ----- ------------- 
C1                    VARCHAR2(2)   
C2                    VARCHAR2(3)   
C3                    VARCHAR2(10)
subquery oracle sql
1 Answers

If the subquery returns null values inside NOT IN ( subquery ), query won't return an rows.

the below SQL returns 12345

With WTH0 AS (
  SELECT '12345' x FROM dual
),
WTH1 AS (
  SELECT '1' c1, '2' c2, '3' c3 FROM dual
)
SELECT X 
FROM WTH0
WHERE X NOT IN (SELECT C1||C2||C3 FROM WTH1);

whereas

With WTH0 AS (
  SELECT '12345' x FROM dual
),
WTH1 AS (
  SELECT '1' c1, '2' c2, '3' c3 FROM dual union
  SELECT null c1, null c2, null c3 FROM dual
)
SELECT X 
FROM WTH0
WHERE X NOT IN (SELECT C1||C2||C3 FROM WTH1);

returns empty result set.

user_11189780
Related