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