|
自连接：将一张表看作两张表
练习：查询员工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;
内连接
只是把左表和右表满足连接条件的数据查出来，其它的数据都没有要！！！
select employee_id,department_name
from employees e join departments d
on e.`department_id`=d.`department_id`
外连接
JOIN … ON
左外连接 left join…on
左外连接，左表和右表满足条件的数据，和左表中不满足条件的数据！！！
练习：查询所有员工的last_name,department_name信息
select last_name,department_name
from employees e left join departments d
on e.`department_id`=d.`department_id`;
右外连接 right join … on
右外连接，右表和左表满足条件的数据，和右表中不满足条件的数据！！！
练习：查询所有员工的last_name,department_name信息
select last_name,department_name
from departments d right join employees e
on e.`department_id`=d.`department_id`;
七种 SQL JOINS 的实现
UNION的使用
合并查询结果
UNION操作符
UNION 操作符返回两个查询的结果集的并集，去除重复记录。
UNION ALL操作符
UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
注意：执行UNION ALL语句时所需要的资源比UNION语句少。
如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
1、内连接（两表只要满足条件的）
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
2、左外连接（左和右满足条件的，和左中不满足条件的）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
3、右外连接（右和左满足条件的，和右中不满足条件的）
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
4、在左外连接的基础上，右表取null值（满足条件的肯定不是nullmssql 左连接，我们不取）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
5、在右外连接的基础上，我们取左表的null值
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
6、右外连接取左表null值，和左外连接合并UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
7、
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
（编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
