联接查询
联接查询是一种常见的数据库操作,即在两张表(或更多表)中进行匹配的操作,一般称之为水平操作。
MySql数据库支持的联接查询:
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
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
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
多表联接
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 );