tags:

views:

23

answers:

2
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;
+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
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
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