oracle自9i之后开始支持 Join 操作，而且支持嵌套，但必须使用括弧，否则会报 from 子句错误。
若要创建一个只包括在被联接字段中具有相同数据的记录，使用 INNER JOIN 操作。
一、LEFT JOIN 或 RIGHT JOIN 可以嵌套到 INNER JOIN 语句中，
         INNER JOIN 语句不能嵌套到 LEFT JOIN 或 RIGHT JOIN 语句中。

二、[/b]对 Join 后的数据集[/b]不能赋别名，赋别名后提示 Join 操作语法错误。

三、LEFT JOIN 或 LEFT OUTER JOIN。
        左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

        一般情况下可以等同看待。

建表a
create table a as select rownum as id,rownum as name from dual connect by level < 10;
建表b
create table b as select rownum+5 as id,rownum+5 as name from dual connect by level < 10;

SQL> select * from a;

        ID       NAME
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9

SQL> select * from b;

        ID       NAME
---------- ----------
         6          6
         7          7
         8          8
         9          9
        10         10
        11         11
        12         12
        13         13
        14         14

两个表a,b相连接,要取出id相同的字段,如下：

1、左连接

SQL> select * from a left join b on a.id=b.id order by a.id;

        ID       NAME         ID       NAME
---------- ---------- ---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6          6          6
         7          7          7          7
         8          8          8          8
         9          9          9          9

左连接：以a表为基础，首先取出a表中所有数据,然后再加上与a,b匹配的的数据

这样写更清晰：

SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a left join b
  2  on a.id=b.id order by a.id;

       AID      ANAME        BID      BNAME
---------- ---------- ---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6          6          6
         7          7          7          7
         8          8          8          8
         9          9          9          9

这样写错误：

SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a left join b
  2  on aid=bid order by aid;
on aid=bid order by aid
       *
ERROR at line 2:
ORA-00904: "BID": invalid identifier

因为上面‘二’说过：对 Join 后的数据集不能赋别名，赋别名后提示 Join 操作语法错误。

这样写也错误：

SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a
  2  left join b
  3  order by a.id
  4  where a.id=b.id;
order by a.id
*
ERROR at line 3:
ORA-00905: missing keyword

order by和group by都不能出现在where前面。

   左外连接

SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a left outer join b
  2  on a.id=b.id order by a.id;

       AID      ANAME        BID      BNAME
---------- ---------- ---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5
         6          6          6          6
         7          7          7          7
         8          8          8          8
         9          9          9          9

2、右连接

SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a  right join b
  2  on a.id=b.id order by b.id;

       AID      ANAME        BID      BNAME
---------- ---------- ---------- ----------
         6          6          6          6
         7          7          7          7
         8          8          8          8
         9          9          9          9
                              10         10
                              11         11
                              12         12
                              13         13
                              14         14

右连接：以b表为基础，首先取出b表中所有数据,然后再加上与a,b匹配的的数据

   右外连接

SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a  right outer join b on a.id=b.id order by b.id;

       AID      ANAME        BID      BNAME
---------- ---------- ---------- ----------
         6          6          6          6
         7          7          7          7
         8          8          8          8
         9          9          9          9
                              10         10
                              11         11
                              12         12
                              13         13
                              14         14

3、内连接

SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a inner join b
  2  on a.id=b.id order by a.id;

       AID      ANAME        BID      BNAME
---------- ---------- ---------- ----------
         6          6          6          6
         7          7          7          7
         8          8          8          8
         9          9          9          9

内连接：不以哪个表为基础，仅取出匹配的数据
 

可以使用多个join连接多个表，在oracle中多个join不用加括号

  FROM t98_indpty_prod_stat   T98
  LEFT JOIN t02_prod_group_rela_h   T02
    ON T98.Product_Id = T02.Product_Id
  LEFT JOIN t99_product_grp_rela_cd  T99
    ON T02.Product_Group_id = T99.Product_Grp_Fouth_Lvl_Cd
  WHERE T98.Summ_Date = '2010-02-28'
  AND T98.Txdate = '2010-02-20'
  GROUP BY t98.Summ_Date,t98.Stat_Org_Id,t98.Conform_Indparty_Id,t98.Province_Cd,t98.Txdate;

但是千万要注意，from子句中的别名，不支持as，select子句支持as

 例子：

SQL> select x.id as hh from a x;

        HH
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

SQL> select x.id as hh from a as x;
select x.id as hh from a as x
                         *
ERROR at line 1:
ORA-00933: SQL command not properly ended

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

SQL Statement 1
UNION ALL
SQL Statement 2

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA