DROP TABLE table1;
DROP TABLE table2;
CREATE TABLE table1
(   col1  NUMBER,
    col2  NUMBER,
    col3  NUMBER,
    col4  NUMBER
);
CREATE TABLE table2
(   col1  NUMBER,
    col2  NUMBER,
    col3  NUMBER,
    col4  NUMBER
);
INSERT INTO TABLE1 VALUES (1,44,1,1);
INSERT INTO table1 VALUES (2,44,2,2);
INSERT INTO TABLE1 VALUES (3,44,3,3);
INSERT INTO table2  VALUES (1,44,11,11);
INSERT INTO TABLE2  VALUES (2,44,22,22);
-- Q1
SELECT t1.*, t2.* FROM table1 t1, table2 t2
WHERE t1.col1 = t2.col1(+);
-- Yields
-- col1 col2 col3 col4 col1_1   col2_1 col3_1 col4_1
      1   44    1    1      1       44     11     11
      2   44    2    2      2       44     22     22
      3   44    3    3      NULL   NULL  NULL   NULL
-- Q2
SELECT t1.*, t2.* FROM table1 t1, table2 t2
WHERE t1.col1 = t2.col1(+)
AND             t2.col1 IS NULL;
-- Yields
-- col1 col2 col3 col4 col1_1   col2_1 col3_1 col4_1
      3   44    3    3   NULL     NULL   NULL   NULL
-- Q3
SELECT t1.*, t2.* FROM table1 t1, table2 t2
WHERE t1.col2 = 44
  AND t2.col2 = 44 
  AND t1.col1 = t2.col1(+)
  AND           t2.col1 IS NULL;
-- Yields
-- col1 col2 col3 col4 col1_1   col2_1 col3_1 col4_1
-- No Rows.
COMMIT;
views:
23answers:
2
                +1 
                A: 
                
                
              Because t2.col1 IS NULL and t2.col2 = 44  will not both be true at the same time.
If t2.col1 IS NULL, it means there was no match on the join, so t2.col2 will also be NULL.
                  RedFilter
                   2010-08-20 18:46:32
                
              Doh'  It's obvious now.  This also explains why breaking the filters into the with clauses, and only doing the join in the resulting query works around the issue.Thanks Red
                  EvilTeach
                   2010-08-20 18:53:10
                
                
                A: 
                
                
              
            t2.col2 can't possibly be 44, as checked for in your WHERE if you're not getting a result back from the left join for this entire table!
                  Will A
                   2010-08-20 18:47:29