ORACLE SQL AND ANSI SQL (common)
来源：互联网 发布：centos 7 nfs安装配置 编辑：程序博客网 时间：2024/06/05 17:17
子查询
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 SYSSQL> 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 SYSSQL> 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 zbkSQL> 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 SYSSQL> 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 zbkSQL> 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 zbkSQL> 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 zbk
SQL> 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 SYS7 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 SYS7 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 SYS7 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 zbk8 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 zbk8 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 tableSQL> 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 SYS8 rows selected.
0 0
- IE下css设置select的诸多问题
- 单例模式
- SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问
- 【学习笔记】ubuntu12.04安装与配置gitolite搭建git服务器
- 调用JQuery.ajax时报错“[object XMLHttpRequest]”的问题
- ORACLE SQL AND ANSI SQL (common)
- C#摄像头编程实例
- Ubuntu 12.04LTS 系统 /etc/default/grub文件详解
- Tomcat性能优化（整理）
- 如何在Oracle中复制表结构和表数据
- android下载脚本repo的新地址
- platform_device and paltform_driver
- cps劫持解决步骤以及方法
- Java中getAttribute和getParameter的区别
