sql92标准仅支持内连接
sql99标准支持内连接+外连接(左外、右外)+交叉连接
按功能分类:
内连接:inner
等值连接
非等值连接
自连接
外连接:
左外连接 left outer
右外连接 right outer
全连接 full outer
交叉连接 cross outer
select 查询的表
from 表1 as 别名
join 表2 as 别名
on 连接条件
[where]
[group]
[having]
[order by]
一:等值连接
案例一:查询女生对应的男朋友
mysql> select name,boyname from boys,beauty
-> where beauty.boyfriend_id=boys.id;
+------------+-----------+
| name | boyname |
+------------+-----------+
| Angelababy | 黄晓明 |
| 热巴 | 鹿晗 |
| 周芷若 | 张无忌 |
| 小昭 | 张无忌 |
| 王语嫣 | 段誉 |
| 赵敏 | 张无忌 |
+------------+-----------+
6 rows in set (0.00 sec)
案例二
查询有奖金的员工名、部门名
mysql> select last_name,department_name,commission_pct
-> from employees e,departments d
-> where e.department_id=d.department_id and commission_pct is not null;
+------------+-----------------+----------------+
| last_name | department_name | commission_pct |
+------------+-----------------+----------------+
| Russell | Sal | 0.40 |
| Partners | Sal | 0.30 |
案例三
查询城市名第二个字符为o最后一个字符为n的部门名和城市名
mysql> select department_name,city
-> from departments d,locations l
-> where d.location_id=l.location_id and city like '_o%n';
+-----------------+--------+
| department_name | city |
+-----------------+--------+
| Hum | London |
+-----------------+--------+
1 row in set (0.00 sec)
二:非等值连接
将等值连接的=号换为其它运算符
三:自连接
mysql> select e.employee_id,e.last_name,m.employee_id,m.last_name
-> from employees e,employees m
-> where e.manager_id=m.employee_id;
+-------------+-------------+-------------+-----------+
| employee_id | last_name | employee_id | last_name |
+-------------+-------------+-------------+-----------+
| 101 | Kochhar | 100 | K_ing |
| 102 | De Haan | 100 | K_ing |
| 103 | Hunold | 102 | De Haan |
内连接
select ~
from ~ as ~
inner join ~ as ~
on ~;
#等值连接
mysql> select last_name,department_name
-> from employees e
-> inner join departments d
-> on e.department_id=d.department_id;
+-------------+-----------------+
| last_name | department_name |
+-------------+-----------------+
| Whalen | Adm |
| Hartstein | Mar |
| Fay | Mar |
mysql> SELECT city,COUNT(*)
-> FROM locations l
-> INNER JOIN departments d
-> ON l.location_id=d.location_id
-> GROUP BY city
-> HAVING COUNT(*)>3;
+---------+----------+
| city | COUNT(*) |
+---------+----------+
| Seattle | 21 |
+---------+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*),department_name
-> FROM departments d
-> INNER JOIN employees e
-> ON d.`department_id`=e.department_id
-> GROUP BY department_name
-> HAVING COUNT(*)>3
-> ORDER BY COUNT(*) DESC;
+----------+-----------------+
| COUNT(*) | department_name |
+----------+-----------------+
| 45 | Shi |
| 34 | Sal |
| 6 | Fin |
| 6 | Pur |
| 5 | IT |
+----------+-----------------+
5 rows in set (0.00 sec)
#非等值连接
mysql> SELECT COUNT(*),grade_level
-> FROM employees AS e
-> INNER JOIN job_grades AS j
-> ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
-> GROUP BY grade_level
-> HAVING COUNT(*)>2
-> ORDER BY COUNT(*) DESC;
+----------+-------------+
| COUNT(*) | grade_level |
+----------+-------------+
| 38 | C |
| 26 | B |
| 24 | A |
| 16 | D |
| 3 | E |
+----------+-------------+
5 rows in set (0.00 sec)
#自连接
mysql> select a.last_name,b.last_name
-> from employees a
-> inner join employees b on a.manager_id=b.employee_id;
+-------------+-----------+
| last_name | last_name |
+-------------+-----------+
| Kochhar | K_ing |
| De Haan | K_ing |
| Hunold | De Haan |
| Ernst | Hunold |
| Austin | Hunold |
外连接
特点:
外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的,则显示匹配的值
如果从表中没有和它匹配的,则显示null
左外连接:left join左边的是主表
右外连接:right join右边的是主表
全外连接:full join,显示内容为两表交叉部分以及两表交叉部分以外的部分,为空用null表示
mysql> select b.name,bo.*
-> from beauty b
-> left outer join boys bo
-> on b.boyfriend_id=bo.id;
+------------+------+-----------+--------+
| name | id | boyName | userCP |
+------------+------+-----------+--------+
| 周芷若 | 1 | 张无忌 | 100 |
| 小昭 | 1 | 张无忌 | 100 |
| 赵敏 | 1 | 张无忌 | 100 |
| 热巴 | 2 | 鹿晗 | 800 |
| Angelababy | 3 | 黄晓明 | 50 |
| 王语嫣 | 4 | 段誉 | 300 |
| 柳岩 | NULL | NULL | NULL |
| 苍老师 | NULL | NULL | NULL |
| 周冬雨 | NULL | NULL | NULL |
mysql> select b.name
-> from beauty b
-> left outer join boys bo
-> on b.boyfriend_id=bo.id
-> where bo.id is null;
+-----------+
| name |
+-----------+
| 柳岩 |
| 苍老师 |
| 周冬雨 |
| 岳灵珊 |
| 双儿 |
| 夏雪 |
+-----------+
mysql> select b.name,bo.*
-> from boys bo
-> right outer join beauty b
-> on b.boyfriend_id=bo.id;
+------------+------+-----------+--------+
| name | id | boyName | userCP |
+------------+------+-----------+--------+
| 周芷若 | 1 | 张无忌 | 100 |
| 小昭 | 1 | 张无忌 | 100 |
| 赵敏 | 1 | 张无忌 | 100 |
| 热巴 | 2 | 鹿晗 | 800 |
| Angelababy | 3 | 黄晓明 | 50 |
| 王语嫣 | 4 | 段誉 | 300 |
| 柳岩 | NULL | NULL | NULL |
| 苍老师 | NULL | NULL | NULL |
| 周冬雨 | NULL | NULL | NULL |
| 岳灵珊 | NULL | NULL | NULL |
查询没有员工的部门
mysql> select d.*,e.employee_id
-> from employees e
-> right outer join departments d
-> on d.department_id=e.department_id
-> where e.employee_id is null;
+---------------+-----------------+------------+-------------+-------------+
| department_id | department_name | manager_id | location_id | employee_id |
+---------------+-----------------+------------+-------------+-------------+
| 120 | Tre | NULL | 1700 | NULL |
| 130 | Cor | NULL | 1700 | NULL |
| 140 | Con | NULL | 1700 | NULL |
| 150 | Sha | NULL | 1700 | NULL |
| 160 | Ben | NULL | 1700 | NULL |
| 170 | Man | NULL | 1700 | NULL |
mysql> select d.*,e.employee_id
-> from departments d
-> left outer join employees e
-> on e.department_id=d.department_id
-> where d.manager_id is null;
+---------------+-----------------+------------+-------------+-------------+
| department_id | department_name | manager_id | location_id | employee_id |
+---------------+-----------------+------------+-------------+-------------+
| 120 | Tre | NULL | 1700 | NULL |
| 130 | Cor | NULL | 1700 | NULL |
| 140 | Con | NULL | 1700 | NULL |
| 150 | Sha | NULL | 1700 | NULL |
| 160 | Ben | NULL | 1700 | NULL |
| 170 | Man | NULL | 1700 | NULL |
自然连接
将两表具有相同属性值的元组相连接
如
SELECT course.*,instructor.*
FROM course NATURAL JOIN instructor;
和
SELECT course.*,instructor.*
FROM course,instructor
WHERE course.`dept_name`=instructor.`dept_name`;
具有相同的查询结果
交叉连接 cross join
类似两表做笛卡尔积
mysql> select b.*,bo.*
-> from beauty b cross join boys bo;
48 rows in set (0.00 sec)
mysql> select count(*) from beauty;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from boys;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)