Oracle 连接 复习

SQL> select * from ta;

        ID NAME
---------- --------------------
         1 gorey
         2 gorey2
SQL> select * from tb;

        ID ADDRESS
---------- --------------------
         2 g2
         3 g3
SQL>

 

 

●左外连接 -》 左表记录全部选中，右表匹配选中 ，未匹配的情况NULL显示

 

SQL> select * from ta left outer join tb on ta.id = tb.id;

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- --------------------
         1 gorey
         2 gorey2                        2 g2
SQL> select * from ta left join tb on ta.id = tb.id;

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- --------------------
         1 gorey
         2 gorey2                        2 g2
SQL> select * from ta,tb where ta.id = tb.id(+);

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- --------------------
         1 gorey
         2 gorey2                        2 g2
SQL>

 

⇒总结： SQL-1999: select * from A left (outer) join B on A.xx = B.xx

         Oracle: select * from A, B where A.xx = B.xx(+);

 

 

●右外连接 与上面相反

 

●全外连接 //  只提供 SQL-1999的写法， 即没有（+）的参与

SQL> select * from ta full outer join tb on ta.id = tb.id;

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- --------------------
         1 gorey
         2 gorey2                        2 g2
                                         3 g3
SQL> select * from ta full join tb on ta.id = tb.id;

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- --------------------
         1 gorey
         2 gorey2                        2 g2
                                         3 g3
SQL>

 

 

 

 

 

/** ----------------------------------------------------------- */

SQL> select * from ta;

        ID NAME
---------- --------------------
         1 gorey
         2 gorey2
SQL> select * from tb;

        ID ADDRESS
---------- --------------------
         2 g2
         3 g3
         2 gorey2
SQL>

●内连接 -> 左表和右表相互匹配， 符合条件的行才被选中，不会有NULL出现

SQL> select * from ta inner join tb on ta.id = tb.id;

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- --------------------
         2 gorey2                        2 g2
         2 gorey2                        2 gorey2
SQL> select * from ta,tb where ta.id = tb.id;

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- --------------------
         2 gorey2                        2 g2
         2 gorey2                        2 gorey2
SQL> select * from ta join tb on ta.id = tb.id;

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- --------------------
         2 gorey2                        2 g2
         2 gorey2                        2 gorey2

 

⇒总结： SQL-1999:select * from A (inner) join B on A.xx = B.xx

           Oracle: select * from A, B where A.xx = B.xx;

 

●自连接： 做连接的两个表，是同一个表，使用别名来实现自己和自己连接

SQL> select * from tc;

        ID NAME                        TID
---------- -------------------- ----------
         1 a                            10
         2 b                            11
         3 c                             1
SQL> select a.id,a.name,b.name from tc a, tc b where a.id = b.tid;

        ID NAME                 NAME
---------- -------------------- --------------------
         1 a                    c
SQL>

 

●交叉连接 笛卡尔积

SQL> select * from ta;

        ID NAME
---------- --------------------
         1 gorey
         2 gorey2
SQL> select * from tb;

        ID ADDRESS
---------- --------------------
         2 g2
         3 g3
         2 gorey2
SQL> select * from ta cross join tb;

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- -------------------
         1 gorey                         2 g2
         2 gorey2                        2 g2
         1 gorey                         3 g3
         2 gorey2                        3 g3
         1 gorey                         2 gorey2
         2 gorey2                        2 gorey2
SQL> select * from ta,tb;

        ID NAME                         ID ADDRESS
---------- -------------------- ---------- --------------------
         1 gorey                         2 g2
         2 gorey2                        2 g2
         1 gorey                         3 g3
         2 gorey2                        3 g3
         1 gorey                         2 gorey2
         2 gorey2                        2 gorey2

 

⇒总结：SQL-1999  select * from A cross join B

           Oracle        select * from A, B