Oracle - Sql - Why doesn't Q3, return the same results as Q2?
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;
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
.
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!
精彩评论