mysql多表连接和子查询

文章实例的数据表，来自上一篇博客《mysql简单查询》：http://blog.csdn.net/zuiwuyuan/article/details/39349611

MYSQL的多表连接建立在有相互关系的父子表上，分为交叉连接、内连接、外连接、自连接

一、交叉连接/笛卡尔交集

SELECT COUNT(*) FROM emp;    # 返回14行
SELECT COUNT(*) FROM dept;   #返回4行
SELECT COUNT(*) FROM emp,dept;         #返回14*4=56行数据
SELECT COUNT(*) FROM emp CROSS JOIN dept;  # 通过关键字CROSS JOIN进行交叉连接

SELECT * FROM emp,dept;
SELECT * FROM emp CROSS JOIN dept;

二、内连接

语法：

select ... from join_table
[inner] JOIN join_table2
[ON join_condition]
where where_definition

只列出这些连接表中与连接条件相匹配的数据行

内连接分类：
等值连接：在连接条件中使用=运算符来比较被连接列的列值
非等值连接：在连接条件中使用除等号以外的其他比较运算符来比较背连接的列的列值
自然连接：在连接条件中使用=运算符来比较被连接列的列值，但它使用选择列表指出查询结果集合中所包含的列，并删除连接表中的重复列

2.1 连接条件就是主外键相连

SELECT * FROM emp e,dept d WHERE e.deptno=d.deptno;

标准的内连接语法

SELECT * FROM emp
INNER JOIN dept
ON  emp.deptno= dept.deptno;

三、外连接
语法：
select... from join_table
(left|right|full) [outer] join join_table2
on join_condition
where where_definition

不仅列出与连接条件相匹配的行，还列出左表（左连接），右表（右连接）或者两个表（全连接）中所有符合where过滤条件的数据行

3.1 左连接
在外连接中，某些不满足条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行
左连接，左表为主表，左边的表记录全部显示，如果没找到记录则补null

SELECT * FROM dept
LEFT JOIN emp
ON dept.deptno=emp.deptno;

SELECT * FROM emp
LEFT JOIN dept
ON emp.deptno=dept.deptno;

3.2 右连接

SELECT * FROM emp
RIGHT JOIN dept
ON dept.deptno=emp.deptno;

3.3 全连接（mysql不支持）

SELECT * FROM emp
FULL JOIN dept
ON dept.deptno=emp.deptno;

四、子查询
在某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询
为了给主查询提供数据而首先执行的查询被叫做子查询
用于子查询的关键字主要包括in、not in、=、<>等

4.1 查询工资比ALLEN搞的员工

SELECT *
FROM emp
WHERE sal>(
SELECT sal FROM emp WHERE ename='ALLEN'
)

4.2 查询和ALLEN同一个部门的员工

SELECT *
FROM emp
WHERE deptno=(
SELECT deptno FROM emp WHERE ename='ALLEN'
)

4.3 查询月薪最高的员工的名字

SELECT ename,sal
FROM emp
WHERE sal=(
SELECT MAX(sal)
FROM emp
)

4.4 单行子查询返回多个结果--错误

SELECT *
FROM emp
WHERE sal=(
SELECT MIN(sal)
FROM emp
GROUP BY deptno
)

4.5 子查询没有返回结果

SELECT *
FROM emp
WHERE job=(
SELECT job
FROM emp
WHERE ename='lining'
)

4.6 in 与列表中的任意成员相等（等于子查询的任意结果）

SELECT *
FROM emp
WHERE sal IN(
SELECT sal
FROM emp
WHERE job='SALESMAN'
)

4.7 ANY 与子查询返回的每一个值比较（这里小于子查询的任一个结果）

SELECT *
FROM emp
WHERE sal<ANY(
SELECT sal
FROM emp
WHERE job='SALESMAN'
)

4.8 ALL与子查询返回的所有值比较（这里要小于子查询的所有结果）

SELECT *
FROM emp
WHERE sal<ALL(
SELECT sal
FROM emp
WHERE job='SALESMAN'
)