标量子查询(一)

标量子查询只和外连接语句等价

点击(此处)折叠或打开

1. --下面的语句是否等价?
   
2. SELECT t1.ID, t2.ID FROM t1, t2 WHERE t1.ID = t2.ID;
   
3. SELECT t1.ID, (SELECT t2.ID FROM t2 WHERE ID = t1.ID) FROM t1;
   
4. 
   
5. --构造环境,进行验证
   
6. DROP TABLE t1;
   
7. CREATE TABLE t1 (ID INT);
   
8. INSERT INTO t1 VALUES (1);
   
9. INSERT INTO t1 VALUES (2);
   
10. INSERT INTO t1 VALUES (3);
    
11. COMMIT;
    
12. 
    
13. DROP TABLE t2;
    
14. CREATE TABLE t2 (ID INT );
    
15. INSERT INTO t2 VALUES (1);
    
16. INSERT INTO t2 VALUES (2);
    
17. INSERT INTO t2 VALUES (NULL);
    
18. COMMIT;
    
19. 
    
20. --执行如下，发现两者并不等价。
    
21. SELECT t1.ID, t2.ID FROM t1, t2 WHERE t1.ID = t2.ID;
    
22.         ID ID
    
23. ---------- ----------
    
24.            1 1
    
25.            2 2
    
26.          
    
27. SELECT t1.ID, (SELECT t2.ID FROM t2 WHERE ID = t1.ID) FROM t1;
    
28. 
    
29.         ID (SELECTT2.IDFROMT2WHEREID=T1.ID)
    
30. ---------- --------------------------------
    
31.          1 1
    
32.          2 2
    
33.          3
    
34. 
    
35. --实际上标量子查询只和外关联语句完全等价，具体试验如下：
    
36. SELECT t1.ID, t2.ID FROM t1, t2 WHERE t1.ID = t2.ID(+);
    
37. 
    
38.         ID ID
    
39. ---------- ----------
    
40.            1 1
    
41.            2 2
    
42.            3
    
43. SELECT t1.ID, (SELECT t2.ID FROM t2 WHERE ID = t1.ID) FROM t1;
    
44. 
    
45.         ID (SELECTT2.IDFROMT2WHEREID=T1.ID)
    
46. ---------- --------------------------------
    
47.            1 1
    
48.            2 2
    
49.            3

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1071303/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1071303/