sql 联接与集合操作

联接查询

联接查询是一种常见的数据库操作，即在两张表（或更多表）中进行匹配的操作，一般称之为水平操作。

  MySql数据库支持的联接查询：

1 、Cross Join (交叉联接)

2、INNER JOIN（内联接）

3、OUTER JOIN（外联接）

    在进行联接操作时，每次联接都只发生在两个表之间，每次产生一个虚拟表，这个虚拟表在依次与from子句中的下一个表进行联接，重复上述不走，知道From子句中的表都被处理完为止。

Cross Join

cross join 对两个表执行笛卡尔积，返回两个表中所有列的组合，若左表有m行数据，右表有n行数据，则cross  join将返回m*n行数据。

   一般在使用cross join将自己与自己进行联接操作时需要指定别名

如： 

SELECT a.num ,b.num form dept_manager as a CROSS JOIN dept_manager AS b

也可以使用下面的ANSI SQL 89语法来实现相同的任务查询

select * from dept_manager as a ,dept_manager as b;

INNER JOIN

inner join 一般用来根据一些过滤条件来匹配表之间的数据。

在逻辑查询的前三个处理阶段中，inner join 应用前两个阶段，即先产生笛卡尔积的虚拟表，再按照ON过滤条件来进行数据的匹配操作。inner join 没有第三步操作，即不添加外部行，这个也是与OUTER JOIN的最大区别之一，也正因不会添加外部行，指定过滤条件在ON子句和WHERE子句中没有任何区别。

如：（其中inner可以省略）

SELECT a.firstname from table1 as a [inner] join table2 as b on a.id = b.id  where age = 21;

该语句等价于：

SELECT a.firstname from table1 as a [inner] join table2 as b on a.id = b.id  and age = 21;

此外如果ON子句中的列具有相同的名称，可以使用USING子句来进行简化，上述例子就可以改为

SELECT a.firstname from table1 as a [inner] join table2 as b USING(id) where age = 21;

OUTER JOIN

通过outer join 用户可以按照一些过滤条件来匹配表之间的数据，与INNER JOIN不同的是，在通过OUTER JOIN 添加的保留表中存在未找到匹配数据。Mysql数据库暂时支持LEFT  OUTER  JOIN 和RIGHT OUTER JOIN 暂时不支持FULL OUTER JOIN ，与inner关键字一样，可以省略OUTER 关键字。

outer join 应用于逻辑查询的钱三个步骤，即产生笛卡尔积的虚拟表，应用ON过滤器和添加外部行。对于保留表中的行数据，如果未找到匹配数据而被添加的记录，其值用NULL进行填充。

如：返回没有用户订单的客户

select  u.user_id from users as u left outer join orders as o on u.user_id = o.user_id where o.order_id is null;

需要注意的是INNER JOIN 的过滤条件都可以写在ON  子句中，二outer join的过滤条件不可以这样处理，否则可能会得到不正确的结果。同样可以使用USING来简化on的子句

select  u.user_id from users as u left outer join orders as o USING(user_id) where o.order_id is null;

NATURAL JOIN

NATURAL JOIN 等同于INNER JOIN 与USING的组合，它隐含的作用是将两个表中具有相同名称的列进行匹配，同样NATURAL LEFT(RIGHT)JOIN等同于

LEFT (RIGHT) OUTER JOIN 与USING的组合。

如对于上面的

SELECT a.firstname from table1 as a [inner] join table2 as b on a.id = b.id  where age = 21;

等价于

SELECT a.firstname from table1 as a natural join table2 as b;

STRAIGHT_JOIN

STRAIGHT_JOIN并不是新的联接类型，而是用户对sql优化器的控制，其等同于join。通过STRAIGHT_JOIN,MySql数据库强制先读取左边的表。

多表联接

对于INNER JOIN 的多表联接查询，可以随意安排表的顺序而不会影响查询的结果。因为优化器会自动根据成本评估出访问表的顺序。

例如：下面的查询返回经历级别的员工的编号、姓名、职称，以及所在部门信息

SELECT a.emp_no,c.firstname,b.title,d.dept_name from dept_manager a join titles b
on a.emp_no = b.emp_no join employees c on c.emp_no = a.emp_no join departments d on d.dept_no = a.dept_no

对于多表之间的INNER JOIN 语句，也可以将进行INNER JOIN 的表和ON 过滤条件放在一起，例如：
SELECT a.emp_no,c.first_name,b.title,d.dept_name from dept_namager a join (title b ,employess c ,deptments d )
on (a.emp_no = b.emp_no and 
c.emp_no = a.emp_no and d.dept_no = a.dept_no );