標量子查詢(一)

LuiseDalian發表於2014-01-16
標量子查詢只和外連線語句等價

點選(此處)摺疊或開啟

  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;

  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;

  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;

  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;

  29.         ID (SELECTT2.IDFROMT2WHEREID=T1.ID)
  30. ---------- --------------------------------
  31.          1 1
  32.          2 2
  33.          3

  35. --實際上標量子查詢只和外關聯語句完全等價，具體試驗如下：
  36. SELECT t1.ID, t2.ID FROM t1, t2 WHERE t1.ID = t2.ID(+);

  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;

  45.         ID (SELECTT2.IDFROMT2WHEREID=T1.ID)
  46. ---------- --------------------------------
  47.            1 1
  48.            2 2
  49.            3

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1071303/，如需轉載，請註明出處，否則將追究法律責任。

相關文章