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;

但下文说到，在大表关联的时候，使用join查询效率更高

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

(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较有效率。

使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示： 

DELETE FROM customerinfo 
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 

　　使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）.. 替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成： 

SELECT * FROM customerinfo 
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 

　　如果使用连接（JOIN）.. 来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下： 

SELECT * FROM customerinfo 
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo. 
CustomerID 
WHERE salesinfo.CustomerID IS NULL 

　　连接（JOIN）.. 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作 

附：oracle笔试题

一、单选题

1. Oracle发出下列select语句：

SQL> select e.empno, e.ename, d.loc

from emp e, dept d

where e.deptno = d.deptno

and substr(e.ename, 1, 1) = ‘S’;

下列哪个语句是Oracle数据库中可用的ANSI兼容等价语句？ 

A．select empno, ename, loc from emp join dept on emp.deptno = dept.deptno where substr(emp.ename, 1, 1) = ‘S’;

B．select empno, ename, loc from emp, dept on emp.deptno = dept.deptno where substr(emp.ename, 1, 1) = ‘S’;

C．select empno, ename, loc from emp join dept where emp.deptno = dept.deptno and substr(emp.ename, 1, 1) = ‘S’;

D．select empno, ename, loc from emp left join dept on emp.deptno = dept.deptno and substr(emp.ename, 1, 1) = ‘S’;

答案：A  

如果sql语句中用逗号连接2个表，则后面的连接条件用where

如果sql语句中用join连接2个表，则后面的连接条件用on