开发者

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!

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜