sql语句的join用法

sql的join分为三种，内连接、外连接、交叉连接。

以下先建2张表，插入一些数据，后续理解起来更方便一些。

create table emp(empno int, name char(20),depart int);
create table depart(dpno int,dpname char(20));
insert into emp values (1,'bell',1);
insert into emp values (2,'smith',2);
insert into emp values (3,'jet',3);
insert into depart values (1,'design');
insert into depart values (2,'database');
insert into depart values (4,'warehouse');

1. 内连接 inner join  仅列出两表能按照join条件连接起来的信息，其他的信息不显示

select a.*,b.* from emp a inner join depart b on a.depart=b.dpno;

empno   name         depart       dpno       dpname

----------------------------------------------------------------------

1              bell                1                   1         design

2              smith            2                   2         database

和如下语句得到的信息是一样的：

 select a.*,b.* from emp a,depart b where a.depart=b.dpno;

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

2. 外连接 outer join

(1)左外连接 left outer join = left join  显示左表的所有记录，右表符合join条件的信息显示，不符合的置空。

select a.*,b.* from emp a left join depart b on a.depart=b.dpno;

empno   name         depart       dpno       dpname

----------------------------------------------------------------------

1              bell                1                   1         design

2              smith            2                   2         database

3              jet                  3                  

(2)右外连接 right outer join = right join 和left join相反，显示右表的所有记录，左表符合join条件的信息显示，不符合的置空。

select a.*,b.* from emp a right join depart b on a.depart=b.dpno;

empno   name         depart       dpno       dpname

----------------------------------------------------------------------

1              bell                1                   1         design

2              smith            2                   2         database

                                                            4         warehouse

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

(3)全外连接 full outer join = full join ，左右两表的信息都全部显示，符合join条件的信息显示，不符合的置空。

select a.*,b.* from emp a full join depart b on a.depart=b.dpno;

empno   name         depart       dpno       dpname

----------------------------------------------------------------------

1              bell                1                   1         design

2              smith            2                   2         database

                                                            4         warehouse

3              jet                  3   

3.交叉连接，也即生成两表的笛卡尔积。得到的记录相当于两表记录的乘积。

select a.*,b.* from emp a cross join depart b ;

empno   name         depart       dpno       dpname

----------------------------------------------------------------------

1              bell                1                   1         design

2              smith            2                   1         design          

3              jet                  3                   1         design

1              bell                1                   2         database

2              smith            2                   2         database          

3              jet                  3                   2         database

1              bell                1                   4         warehouse

2              smith            2                   4         warehouse

3              jet                  3                   4         warehouse

相当于如下语句：

select a.*,b.* from emp a,depart b;

需要注意的是交叉连接后只能跟where，不可以跟on。

如下语句是错的：

select a.*,b.* from emp a cross join depart b on a.depart=b.dpno;

要修改为这样的才正确：

select a.*,b.* from emp a cross join depart b where a.depart=b.dpno;

一般来讲，在大表关联的时候，建议使用inner join或者left join，不建议使用cross join或者where

比如：

select a.*,b.* from emp a,depart b where a.depart=b.dpno ;                                     A     ---不推荐

select a.*,b.* from emp a cross join depart b where a.depart=b.dpno ;                  B     ---不推荐

select a.*,b.* from emp a inner join depart b on a.depart = b.dpno;                         C    ----推荐

这是因为A,B中，sql需要先对两表生成笛卡尔积，然后才根据where后的条件进行判断，而使用C则不需要。所以C较有效率。

oracle Join操作语法错误和Join语句的嵌套

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;

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 UNION 操作符

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