9i: SELECT regtest.phone FROM user_friend LEFT JOIN regtest ON user_friend.fphone=regtest.phone; 8i: SELECT regtest.phone FROM user_friend,regtest where user_friend.fphone=regtest.phone(+);
语句:SQL> select * from a1,a2 where a1.bbb=a2.bbb(+);和SQL> select * from a1,a2 where a2.bbb(+)=a1.bbb;执行的结果应该没有区别,那么执行效率呢?区别在哪里?
对sql server的语法不是很熟。
SELECT regtest.phone FROM user_friend,regtest where user_friend.fphone=regtest.phone(+);
对应着SQL的LEFT吗?
SQL> select * from a1;BBB
----------
101
102
103
104
105SQL> select * from a2;BBB CCC
---------- --------------------
101
102
105SQL> select * from a1,a2 where a1.bbb(+)=a2.bbb;BBB BBB CCC
---------- ---------- --------------------
101 101
102 102
105 105SQL> select * from a1,a2 where a1.bbb=a2.bbb(+);BBB BBB CCC
---------- ---------- --------------------
101 101
102 102
103
104
105 105SQL>
(+)放右边表示左连接
(+)放左边表示右连接
ORACLE 的写法就是上面的高手们写的左外连接。
楼上说的没错,9i支持left join的写法。
SELECT regtest.phone FROM user_friend LEFT JOIN regtest ON user_friend.fphone=regtest.phone;
8i:
SELECT regtest.phone FROM user_friend,regtest where user_friend.fphone=regtest.phone(+);
sConnect = "Provider=MSDAORA.1;" & _
"Data Source=" & sServer & ";User Id=" & sUser & ";Password=" & sPassword & ";"'Oracle Provider for OLE DB
sConnect = "Provider=OraOLEDB.Oracle.1;" & _
"Data Source=" & sServer & ";User Id=" & sUser & ";Password=" & sPassword & ";"sConnect = "Provider=MSDAORA.1;" & _
"Data Source=" & sServer & ";User Id=" & sUser & ";Password=" & sPassword & ";"adoConnect.ConnectionString = sConnect
adoConnect.Open