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