MYSQL-多表查询

等值连接和非等值连接

• 等值连接：连接条件使用的是 =(等号)进行连接的

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

• 非等值连接：连接条件使用的是不是=

SELECT
	e.employee_id,
	e.last_name,
	e.department_id,
	e.salary
FROM
	employees e,
	job_grades j
WHERE
	e.salary BETWEEN j.lowest_sal
AND j.highest_sal;

自连接和非自连接

• 自连接：同一种表自己与自己进行连接

SELECT
	emp.employee_id,
	emp.last_name,
	emp.manager_id,
	mgr.last_name
FROM
	employees emp,
	employees mgr
WHERE
	emp.manager_id = mgr.employee_id;

• 非自连接：两张不同的表进行连接

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

内连接和外连接

内连接

• 格式1

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

• 格式2

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

• 格式3

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

多表内连接的写法一：

SELECT
	e.employee_id,e.last_name,e.department_id,e.salary,l.location_id
FROM
	employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id;

多表内连接的写法二：

SELECT
	e.employee_id,
	e.last_name,
	e.department_id
FROM
	employees e
INNER JOIN departments d
INNER JOIN jobs j ON e.department_id = d.department_id
AND e.job_id = j.job_id;

外连接

左外连接

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

SELECT
	e.employee_id,
	e.first_name,
	e.department_id
FROM
	employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

SELECT
	e.employee_id,
	e.first_name,
	e.department_id
FROM
	employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE
	ISNULL(e.department_id);

右外连接

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

SELECT
	e.employee_id,
	e.first_name,
	e.department_id
FROM
	employees e
RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;

UNION和UNION ALL

• 将两个 select 语句的结果作为一个整体显示出来，我们就需要用到UNION和UNION ALL关键字

UNION：会执行去重操作

SELECT
	e.employee_id,
	e.first_name,
	e.department_id
FROM
	employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
	SELECT
		e.employee_id,
		e.first_name,
		e.department_id
	FROM
		employees e
	LEFT JOIN departments d ON e.department_id = d.department_id;

UNION ALL:不会执行去重操作

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

NATURAL JOIN

• NATURAL JOIN：自然连接，它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接`

SELECT
	employee_id,
	last_name,
	department_name
FROM
	employees e
NATURAL JOIN departments d;

• 相当于如下sql：

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

USING

• 只能用在两张表的同名字段进行等值连接。如果连接条件的字段不同名 (如，自连接) ，就无法使用 USING 连接

SELECT
	e.employee_id,
	e.last_name,
	e.department_id
FROM
	employees e
JOIN departments d USING (department_id);