sql语句的join用法

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, aname,b.id bid, 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, aname,b.id bid, 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, aname,b.id bid, 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前⾯。

oracle⾃9i之后开始⽀持 Join 操作，⽽且⽀持嵌套，但必须使⽤括弧，否则会报 from ⼦句错误。

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, aname,b.id bid, 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, aname,b.id bid, 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, aname,b.id bid, 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, aname,b.id bid, 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, aname,b.id bid, 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, aname,b.id bid, 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, aname,b.id bid, 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
－－－－－－－－－－－－－－－－－［以下为⽹上的⼀点资料］－－－－－－－－－－－－－－－
LEFT JOIN操作⽤于在任何的 FROM ⼦句中，组合来源表的记录。

使⽤ LEFT JOIN 运算来创建⼀个左边外部联接。

左边外部联接将包含了从第⼀个（左边）开始的两个表中的全部记录，即使在第⼆个（右边）表中并没有相符值的记录。

不同的 SQL JOIN
除了我们在上⾯的例⼦中使⽤的 INNER JOIN（内连接），我们还可以其他⼏种连接。

下⾯列出了您可以使⽤的 JOIN 类型，以及它们之间的差异。

JOIN: 如果表中有⾄少⼀个匹配，则返回⾏，join和inner join相同。

LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的⾏
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的⾏
FULL JOIN: 只要其中⼀个表中存在匹配，就返回⾏
---------------------------------
SQL UNION 和 UNION ALL 操作符
SQL UNION 操作符
UNION 操作符⽤于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。

列也必须拥有相似的数据类型。

同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法
SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
注释：默认地，UNION 操作符选取不同的值。

如果允许重复的值，请使⽤ UNION ALL。

SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
另外，UNION 结果集中的列名总是等于 UNION 中第⼀个 SELECT 语句中的列名。

下⾯的例⼦中使⽤的原始表：
Employees_China:
E_ID E_Name
01Zhang, Hua
02Wang, Wei
03Carter, Thomas
04Yang, Ming
Employees_USA:
E_ID E_Name
01Adams, John
02Bush, George
03Carter, Thomas
04Gates, Bill
使⽤ UNION 命令
实例
列出所有在中国和美国的不同的雇员名：
SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA
结果
E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill
注释：这个命令⽆法列出在中国和美国的所有雇员。

在上⾯的例⼦中，我们有两个名字相同的雇员，他们当中只有⼀个⼈被列出来了。

UNION 命令只会选取不同的值。

UNION ALL
UNION ALL 命令和 UNION 命令⼏乎是等效的，不过 UNION ALL 命令会列出所有的值。

SQL Statement 1 UNION ALL SQL Statement 2
使⽤ UNION ALL 命令
实例：
列出在中国和美国的所有的雇员：
SELECT E_Name FROM Employees_China UNION ALL SELECT E_Name FROM Employees_USA
结果
E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill。