多表查询的分类
这个分类是根据多表查询时的连接条件而划分的，即where语句后的内容，可以用三个角度进行划分：
- 角度1：等值连接 非等值连接
- 角度2： 自连接 非自连接
- 角度3：内连接 外连接
在多表查询时，需要注意一些问题：
- 多表查询的正确方式：需要有连接条件。如果没有连接条件，就会出现笛卡尔积错误，即查询出两个表之间的所有的数据组合结果
SELECT employee_id,department_name
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.department_id;
- 如果查询语句中出现了多个表中都存在的字段。则必须指明此字段所在的表（即下面的
FROM语句）
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;
- 可以给表起别名，然后在
SELECT和WHERE语句中进行使用（指定这个字段来自哪）
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.department_id;
- 一旦起了别名，并且在
SELECT和WHERE中使用了的话，就必须使用别名，不能使用表的原名，否则会报错。 - 如果有n个表实现多表的查询，则需要至少n-1个连接条件，比如查询三个表需要两个连接条件.
等值连接 vs 非等值连接
等值连接：查询A表中的某个字段=B表中的某个字段
比如查询员工姓名和部分，只需要员工表中的部门id等于部门表中的部门id
SELECT e.employee_id,e.last_name,d.department_name,l.city,e.department_id,l.location_id
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;
非等值连接：连接条件是一个范围
非等值连接的例子：
SELECT *
FROM job_grades;
SELECT last_name,salary,grade_level
FROM employees e,job_grades j
#where e.`salary` between j.`lowest_sal` and j.`highest_sal`;
WHERE e.`salary`>=j.`lowest_sal` AND e.`salary`<=j.`highest_sal`;
自连接 vs 非自连接
自连接：自己连接自己
表1和表2实际上是一张表，用取别名的方式虚拟成两张表，然后进行查询。
SELECT *FROM employees;
举例：查询员工id,员工姓名和其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.`manager_id`=mgr.`employee_id`;//当管理者id==员工id
非自连接：多表查询时的n张表并不是同一张表。
大部分情况下多表查询都是非自连接的.
内连接 vs 外连接
内连接：合并具有同一列的两个以上的表的行 结果集中不包含一个表与另一个表不匹配的行
简单理解就是查询两张表之间符合条件的记录，外连接就是除了查询这个符合条件的记录，还可以查询其中一张表不符合条件的记录
SELECT employee_id,department_name
FROM employees e,departments d
#连接条件
WHERE e.`department_id`=d.`department_id`;
外连接：合并具有同一列的两个以上的表的行，结果集除了包含两个表相匹配的行之外，还包含左表或右表中不匹配的行。
（类似于两个集合取交集，交集部分就是内连接能查到的部分，外连接就是左集合+交集 或者 右集合+交集 后者 左右集合全部）
外连接的分类：左外连接、右外连接、满外连接
SQL92和SQL99语法如何实现内外连接
SQL92实现内连接
就是我们常用的写法
SELECT employee_id,department_name
FROM employees e,departments d
#连接条件
WHERE e.`department_id`=d.`department_id`;
SQL92实现外连接
外连接有左外和右外、满外。不论是哪一种，在查询数据少的一边添加一个’+'即可，就像两腿走路，一条腿短一点，给短腿添加一个支架即可。
实现左外，就给右表补个+号
举例：
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`(+);
遗憾的是，MySQL不支持SQL92中外连接的写法，即上面这个写法在mysql中用不了，但这个写法在Oracle中可以使用
在SQL99语法中可以使用JOIN.....ON来实现多表查询，这种方式可以解决外连接的问题，并且MySQL支持这种语法。
JOIN...ON字句的使用：
SELECT 表1.某字段,表2.某字段，表3.某字段
FROM 表1
JOIN 表2 ON 表1和表2的连接条件
JOIN 表3 ON 表2和表3的连接条件
SQL99语法实现内连接
查询员工的姓名和部门
mysql> SELECT last_name,department_name FROM employees e JOIN departments d ON e.`department_id`=d.`department_id`;
+-------------+------------------+
| last_name | department_name |
+-------------+------------------+
| Whalen | Administration |
| Hartstein | Marketing |
| Fay | Marketing |
| Raphaely | Purchasing |
| Khoo | Purchasing |
| Baida | Purchasing |
| Tobias | Purchasing |
| Himuro | Purchasing |
| Colmenares | Purchasing |
| Mavris | Human Resources |
| Weiss | Shipping |
| Fripp | Shipping |
| Kaufling | Shipping |
| Vollman | Shipping |
| Mourgos | Shipping |
| Nayer | Shipping |
| Mikkilineni | Shipping |
| Landry | Shipping |
| Markle | Shipping |
| Bissot | Shipping |
| Atkinson | Shipping |
| Marlow | Shipping |
| Olson | Shipping |
| Mallin | Shipping |
| Rogers | Shipping |
| Gee | Shipping |
| Philtanker | Shipping |
| Ladwig | Shipping |
| Stiles | Shipping |
| Seo | Shipping |
| Patel | Shipping |
| Rajs | Shipping |
| Davies | Shipping |
| Matos | Shipping |
| Vargas | Shipping |
| Taylor | Shipping |
| Fleaur | Shipping |
| Sullivan | Shipping |
| Geoni | Shipping |
| Sarchand | Shipping |
| Bull | Shipping |
| Dellinger | Shipping |
| Cabrio | Shipping |
| Chung | Shipping |
| Dilly | Shipping |
| Gates | Shipping |
| Perkins | Shipping |
| Bell | Shipping |
| Everett | Shipping |
| McCain | Shipping |
| Jones | Shipping |
| Walsh | Shipping |
| Feeney | Shipping |
| OConnell | Shipping |
| Grant | Shipping |
| Hunold | IT |
| Ernst | IT |
| Austin | IT |
| Pataballa | IT |
| Lorentz | IT |
| Baer | Public Relations |
| Russell | Sales |
| Partners | Sales |
| Errazuriz | Sales |
| Cambrault | Sales |
| Zlotkey | Sales |
| Tucker | Sales |
| Bernstein | Sales |
| Hall | Sales |
| Olsen | Sales |
| Cambrault | Sales |
| Tuvault | Sales |
| King | Sales |
| Sully | Sales |
| McEwen | Sales |
| Smith | Sales |
| Doran | Sales |
| Sewall | Sales |
| Vishney | Sales |
| Greene | Sales |
| Marvins | Sales |
| Lee | Sales |
| Ande | Sales |
| Banda | Sales |
| Ozer | Sales |
| Bloom | Sales |
| Fox | Sales |
| Smith | Sales |
| Bates | Sales |
| Kumar | Sales |
| Abel | Sales |
| Hutton | Sales |
| Taylor | Sales |
| Livingston | Sales |
| Johnson | Sales |
| King | Executive |
| Kochhar | Executive |
| De Haan | Executive |
| Greenberg | Finance |
| Faviet | Finance |
| Chen | Finance |
| Sciarra | Finance |
| Urman | Finance |
| Popp | Finance |
| Higgins | Accounting |
| Gietz | Accounting |
+-------------+------------------+
106 rows in set (0.00 sec)
查询员工的姓名和部门及工作城市
mysql> SELECT last_name,department_name,city FROM employees e JOIN departments d ON e.`department_id`=d.`department_id` JOIN locations l ON d.`location_id`=l.`location_id`;
+-------------+------------------+---------------------+
| last_name | department_name | city |
+-------------+------------------+---------------------+
| Whalen | Administration | Seattle |
| Hartstein | Marketing | Toronto |
| Fay | Marketing | Toronto |
| Raphaely | Purchasing | Seattle |
| Khoo | Purchasing | Seattle |
| Baida | Purchasing | Seattle |
| Tobias | Purchasing | Seattle |
| Himuro | Purchasing | Seattle |
| Colmenares | Purchasing | Seattle |
| Mavris | Human Resources | London |
| Weiss | Shipping | South San Francisco |
| Fripp | Shipping | South San Francisco |
| Kaufling | Shipping | South San Francisco |
| Vollman | Shipping | South San Francisco |
| Mourgos | Shipping | South San Francisco |
| Nayer | Shipping | South San Francisco |
| Mikkilineni | Shipping | South San Francisco |
| Landry | Shipping | South San Francisco |
| Markle | Shipping | South San Francisco |
| Bissot | Shipping | South San Francisco |
| Atkinson | Shipping | South San Francisco |
| Marlow | Shipping | South San Francisco |
| Olson | Shipping | South San Francisco |
| Mallin | Shipping | South San Francisco |
| Rogers | Shipping | South San Francisco |
| Gee | Shipping | South San Francisco |
| Philtanker | Shipping | South San Francisco |
| Ladwig | Shipping | South San Francisco |
| Stiles | Shipping | South San Francisco |
| Seo | Shipping | South San Francisco |
| Patel | Shipping | South San Francisco |
| Rajs | Shipping | South San Francisco |
| Davies | Shipping | South San Francisco |
| Matos | Shipping | South San Francisco |
| Vargas | Shipping | South San Francisco |
| Taylor | Shipping | South San Francisco |
| Fleaur | Shipping | South San Francisco |
| Sullivan | Shipping | South San Francisco |
| Geoni | Shipping | South San Francisco |
| Sarchand | Shipping | South San Francisco |
| Bull | Shipping | South San Francisco |
| Dellinger | Shipping | South San Francisco |
| Cabrio | Shipping | South San Francisco |
| Chung | Shipping | South San Francisco |
| Dilly | Shipping | South San Francisco |
| Gates | Shipping | South San Francisco |
| Perkins | Shipping | South San Francisco |
| Bell | Shipping | South San Francisco |
| Everett | Shipping | South San Francisco |
| McCain | Shipping | South San Francisco |
| Jones | Shipping | South San Francisco |
| Walsh | Shipping | South San Francisco |
| Feeney | Shipping | South San Francisco |
| OConnell | Shipping | South San Francisco |
| Grant | Shipping | South San Francisco |
| Hunold | IT | Southlake |
| Ernst | IT | Southlake |
| Austin | IT | Southlake |
| Pataballa | IT | Southlake |
| Lorentz | IT | Southlake |
| Baer | Public Relations | Munich |
| Russell | Sales | Oxford |
| Partners | Sales | Oxford |
| Errazuriz | Sales | Oxford |
| Cambrault | Sales | Oxford |
| Zlotkey | Sales | Oxford |
| Tucker | Sales | Oxford |
| Bernstein | Sales | Oxford |
| Hall | Sales | Oxford |
| Olsen | Sales | Oxford |
| Cambrault | Sales | Oxford |
| Tuvault | Sales | Oxford |
| King | Sales | Oxford |
| Sully | Sales | Oxford |
| McEwen | Sales | Oxford |
| Smith | Sales | Oxford |
| Doran | Sales | Oxford |
| Sewall | Sales | Oxford |
| Vishney | Sales | Oxford |
| Greene | Sales | Oxford |
| Marvins | Sales | Oxford |
| Lee | Sales | Oxford |
| Ande | Sales | Oxford |
| Banda | Sales | Oxford |
| Ozer | Sales | Oxford |
| Bloom | Sales | Oxford |
| Fox | Sales | Oxford |
| Smith | Sales | Oxford |
| Bates | Sales | Oxford |
| Kumar | Sales | Oxford |
| Abel | Sales | Oxford |
| Hutton | Sales | Oxford |
| Taylor | Sales | Oxford |
| Livingston | Sales | Oxford |
| Johnson | Sales | Oxford |
| King | Executive | Seattle |
| Kochhar | Executive | Seattle |
| De Haan | Executive | Seattle |
| Greenberg | Finance | Seattle |
| Faviet | Finance | Seattle |
| Chen | Finance | Seattle |
| Sciarra | Finance | Seattle |
| Urman | Finance | Seattle |
| Popp | Finance | Seattle |
| Higgins | Accounting | Seattle |
| Gietz | Accounting | Seattle |
+-------------+------------------+---------------------+
106 rows in set (0.00 sec)
join 某个表 on 连接条件
SQL99实现外连接
实现左外连接 使用left outer join
这里的outer可加可不加
可以直接使用 left join
查询到的结果是 左表+两表交集
mysql> SELECT last_name,department_name
-> FROM employees e LEFT OUTER JOIN departments d
-> ON e.`department_id`=d.`department_id`;
+-------------+------------------+
| last_name | department_name |
+-------------+------------------+
| King | Executive |
| Kochhar | Executive |
| De Haan | Executive |
| Hunold | IT |
| Ernst | IT |
| Austin | IT |
| Pataballa | IT |
| Lorentz | IT |
| Greenberg | Finance |
| Faviet | Finance |
| Chen | Finance |
| Sciarra | Finance |
| Urman | Finance |
| Popp | Finance |
| Raphaely | Purchasing |
| Khoo | Purchasing |
| Baida | Purchasing |
| Tobias | Purchasing |
| Himuro | Purchasing |
| Colmenares | Purchasing |
| Weiss | Shipping |
| Fripp | Shipping |
| Kaufling | Shipping |
| Vollman | Shipping |
| Mourgos | Shipping |
| Nayer | Shipping |
| Mikkilineni | Shipping |
| Landry | Shipping |
| Markle | Shipping |
| Bissot | Shipping |
| Atkinson | Shipping |
| Marlow | Shipping |
| Olson | Shipping |
| Mallin | Shipping |
| Rogers | Shipping |
| Gee | Shipping |
| Philtanker | Shipping |
| Ladwig | Shipping |
| Stiles | Shipping |
| Seo | Shipping |
| Patel | Shipping |
| Rajs | Shipping |
| Davies | Shipping |
| Matos | Shipping |
| Vargas | Shipping |
| Russell | Sales |
| Partners | Sales |
| Errazuriz | Sales |
| Cambrault | Sales |
| Zlotkey | Sales |
| Tucker | Sales |
| Bernstein | Sales |
| Hall | Sales |
| Olsen | Sales |
| Cambrault | Sales |
| Tuvault | Sales |
| King | Sales |
| Sully | Sales |
| McEwen | Sales |
| Smith | Sales |
| Doran | Sales |
| Sewall | Sales |
| Vishney | Sales |
| Greene | Sales |
| Marvins | Sales |
| Lee | Sales |
| Ande | Sales |
| Banda | Sales |
| Ozer | Sales |
| Bloom | Sales |
| Fox | Sales |
| Smith | Sales |
| Bates | Sales |
| Kumar | Sales |
| Abel | Sales |
| Hutton | Sales |
| Taylor | Sales |
| Livingston | Sales |
| Grant | NULL |
| Johnson | Sales |
| Taylor | Shipping |
| Fleaur | Shipping |
| Sullivan | Shipping |
| Geoni | Shipping |
| Sarchand | Shipping |
| Bull | Shipping |
| Dellinger | Shipping |
| Cabrio | Shipping |
| Chung | Shipping |
| Dilly | Shipping |
| Gates | Shipping |
| Perkins | Shipping |
| Bell | Shipping |
| Everett | Shipping |
| McCain | Shipping |
| Jones | Shipping |
| Walsh | Shipping |
| Feeney | Shipping |
| OConnell | Shipping |
| Grant | Shipping |
| Whalen | Administration |
| Hartstein | Marketing |
| Fay | Marketing |
| Mavris | Human Resources |
| Baer | Public Relations |
| Higgins | Accounting |
| Gietz | Accounting |
+-------------+------------------+
107 rows in set (0.00 sec)
右外连接 (right outer on)
SELECT e.last_name ,e.department_id,d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id=d.department_id;
满外连接 (full outer join)
满外连接只需要将上面的right outer join 改为 full outer join即可
满外连接返回的结果：左右表的交集+左表剩余部分+右表剩余部分
但是遗憾的是Mysql不支持FULL JOIN ,只能在Oracle中使用
但可以使用 left join union right join 代替
扫一扫
7773
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
