谷加伟 多表查询全部

#中图：内连接

SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

SELECT employee_id,department_name
FROM employees e , departments d
WHERE e.department_id = d.department_id;

#左外连接

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
on e.department_id = d.department_id;

#右外链接

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
on e.department_id = d.department_id;

#左中

SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN
departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

#右中

SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN
departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

#左上+右中
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
on e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN
departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

#右上+左中
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
on e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN
departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

#左右各一半

#左中+右中

SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN
departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN
departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;