ORACLE SQL AND ANSI SQL (common)

最新推荐文章于 2024-11-14 19:49:09 发布

原创最新推荐文章于 2024-11-14 19:49:09 发布 · 922 阅读

0 ·

CC 4.0 BY-SA版权

本文通过实际的SQL查询示例详细介绍了不同类型的SQL连接操作，包括内连接、左连接、右连接及全连接，并展示了如何使用这些连接来获取所需的数据。

子查询

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                 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                        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.