子查询
SQL> select * from t3 where exists (select 1 from t4 where t3.object_id=t4.object_id);
OBJECT_ID OBJECT_TYPE STATUS
---------- ------------------- -------
13 UET$ SYS
9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS
SQL> select * from t3 where exists (select 1 from t4 where t4.object_id in t3.object_id);
OBJECT_ID OBJECT_TYPE STATUS
---------- ------------------- -------
13 UET$ SYS
9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS
SQL> select * from t3 where exists (select 1 from t4 where t3.object_id!=t4.object_id);
OBJECT_ID OBJECT_TYPE STATUS
---------- ------------------- -------
13 UET$ SYS
9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS
88 zbk zbk
SQL> select * from t3 where exists (select 1 from t4 where t4.object_id not in t3.object_id);
OBJECT_ID OBJECT_TYPE STATUS
---------- ------------------- -------
13 UET$ SYS
9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS
88 zbk zbk等值连接
SQL> select * from t3,t4 where t3.object_id=t4.object_id;
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
SQL> select * from t3 join t4 using (object_id);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_NAME OWNER
---------- ------------------- ------- ------------------------------ ------------------------------
13 UET$ SYS UET$ SYS
9 I_FILE#_BLOCK# SYS I_FILE#_BLOCK# SYS
43 I_FILE1 SYS I_FILE1 SYS
SQL> select * from t3 join t4 on (t3.object_id=t4.object_id);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
外连接
SQL> select * from t3 left outer join t4 on (t3.object_id=t4.object_id);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
88 zbk zbk
SQL> select * from t3 left join t4 on (t3.object_id=t4.object_id);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
88 zbk zbk
SQL> select * from t3,t4 where t3.object_id=t4.object_id(+);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
88 zbk zbkSQL> select * from t3 right outer join t4 on (t3.object_id=t4.object_id);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
38 I_OBJ3 SYS
51 I_CON1 SYS
7 I_TS# SYS
56 I_CDEF4 SYS
7 rows selected.
SQL> select * from t3 right join t4 on (t3.object_id=t4.object_id);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
38 I_OBJ3 SYS
51 I_CON1 SYS
7 I_TS# SYS
56 I_CDEF4 SYS
7 rows selected.
SQL> select * from t3,t4 where t3.object_id(+)=t4.object_id;
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
38 I_OBJ3 SYS
51 I_CON1 SYS
7 I_TS# SYS
56 I_CDEF4 SYS
7 rows selected.
全连接
SQL> select * from t3 full outer join t4 on (t3.object_id=t4.object_id);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
51 I_CON1 SYS
38 I_OBJ3 SYS
7 I_TS# SYS
56 I_CDEF4 SYS
88 zbk zbk
8 rows selected.
SQL> select * from t3 full join t4 on (t3.object_id=t4.object_id);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
13 UET$ SYS 13 UET$ SYS
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
51 I_CON1 SYS
38 I_OBJ3 SYS
7 I_TS# SYS
56 I_CDEF4 SYS
88 zbk zbk
8 rows selected.
ORACLE不支持这种写法，而应用上面的full jjoin
QL> select * from t3 ,t4 where t3.object_id(+)=t4.object_id(+);
select * from t3 ,t4 where t3.object_id(+)=t4.object_id(+)
*
ERROR at line 1:
ORA-01468: a predicate may reference only one outer-joined table
SQL> select * from t3,t4 where t3.object_id(+)=t4.object_id
2 union
3 select * from t3,t4 where t3.object_id=t4.object_id(+);
OBJECT_ID OBJECT_TYPE STATUS OBJECT_ID OBJECT_NAME OWNER
---------- ------------------- ------- ---------- ------------------------------ ------------------------------
9 I_FILE#_BLOCK# SYS 9 I_FILE#_BLOCK# SYS
13 UET$ SYS 13 UET$ SYS
43 I_FILE1 SYS 43 I_FILE1 SYS
88 zbk zbk
7 I_TS# SYS
38 I_OBJ3 SYS
51 I_CON1 SYS
56 I_CDEF4 SYS
8 rows selected.
1051
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
