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前面。
oracle自9i之后开始支持 Join 操作,而且支持嵌套,但必须使用括弧,否则会报 from 子句错误。 三、LEFT JOIN 或 LEFT OUTER JOIN。 一般情况下可以等同看待。 建表a SQL> select * from a; ID NAME SQL> select * from b; ID NAME 两个表a,b相连接,要取出id相同的字段,如下:
1、左连接 SQL> select * from a left join b on a.id=b.id order by a.id; ID NAME ID NAME 左连接:以a表为基础,首先取出a表中所有数据,然后再加上与a,b匹配的的数据 这样写更清晰: SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a left join b AID ANAME BID BNAME 这样写错误: SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a left join b 因为上面‘二’说过:对 Join 后的数据集不能赋别名,赋别名后提示 Join 操作语法错误。 这样写也错误: SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a
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 AID ANAME BID BNAME
2、右连接 SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a right join b AID ANAME BID BNAME 右连接:以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
3、内连接 SQL> select a.id aid,a.name aname,b.id bid,b.name bname from a inner join b AID ANAME BID BNAME
内连接:不以哪个表为基础,仅取出匹配的数据 可以使用多个join连接多个表,在oracle中多个join不用加括号 FROM t98_indpty_prod_stat T98 但是千万要注意,from子句中的别名,不支持as,select子句支持as 例子: SQL> select x.id as hh from a x; HH
SQL> select x.id as hh from a as x;
|
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 |
---|---|
01 | Zhang, Hua |
02 | Wang, Wei |
03 | Carter, Thomas |
04 | Yang, Ming |
Employees_USA:
E_ID | E_Name |
---|---|
01 | Adams, John |
02 | Bush, George |
03 | Carter, Thomas |
04 | Gates, 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 |
来源:http://blog.sina.com.cn/s/blog_61c006ea0100l88i.html