小徐带你了解sql99中内连接外连接的用法

sql99语法：

语法：

	select		查询列表
		from		表1	别名	【连接类型】
		join		表2	别名	
		on			  连接条件
		where		  筛选条件
		group by	【分组】	
		having		【筛选条件】
		order by 	  【排序列表】

分类：

内连接：inner
外连接
		左外：left [outer]
		右外：right [outer]
		全外：full [outer]
 交叉连接：cross

内连接语法：

	sleect		查询列表
		from		表1
		inner	join 	表2
		on	连接条件

内连接分类

	等值
	非等值
	自连接

内连接特点：

一：添加排序，分组，筛选
二：inner 可以省略
三：筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读。
四：inner连接和sql92语法中的等值连接效果是一样的。

内连接中等值连接案例：

查询员工名，部门名

SELECT
	last_name,
	departments.department_name 
FROM
	departments
	INNER JOIN employees ON employees.department_id = departments.department_id;

查询名字中包含e的员工名和工种名(添加筛选条件)

SELECT
	last_name,
	departments.department_name 
FROM
	departments
	INNER JOIN employees ON employees.department_id = departments.department_id 
WHERE
	last_name LIKE '%e%';

查询部门个数>3的城市名和部门个数(添加分组+筛选）

SELECT
	city,
	COUNT(*) 
FROM
	departments
	INNER JOIN locations ON departments.location_id = locations.location_id 
GROUP BY
	city 
HAVING
	count(*)> 3;

查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序

SELECT
	department_name,
	COUNT(*) 
FROM
	departments
	INNER JOIN employees ON departments.department_id = employees.department_id 
GROUP BY
	departments.department_id 
HAVING
	count(*)> 3;

查询员工名，部门名，工种名，并按部门名降序

SELECT
	last_name,
	department_name,
	job_title 
FROM
	employees
	INNER JOIN departments ON employees.department_id = departments.department_id
	INNER JOIN jobs ON employees.job_id = jobs.job_id 
GROUP BY
	department_name DESC;

内连接中非等值连接

查询员工的工资级别

SELECT
	salary,
	grade_level 
FROM
	employees
	JOIN job_grades ON employees.salary BETWEEN job_grades.lowest_sal 
	AND job_grades.highest_sal;

查询工资级别的个数>20的个数，并且按工资级别降序

SELECT
	salary,
	grade_level,
	COUNT(*) 
FROM
	employees
	JOIN job_grades ON employees.salary BETWEEN job_grades.lowest_sal 
	AND job_grades.highest_sal 
GROUP BY
	grade_level 
HAVING
	COUNT(*) > 20;

内连接中自连接案例重点难点

案例：查询员工的名字，上级的名字

SELECT
	e.last_name,
	m.last_name 
FROM
	employees e
	JOIN employees m ON e.employee_id = m.employee_id;

查询员工的名字包含k的员工，上级的名字

SELECT
	e.last_name,
	m.last_name 
FROM
	employees e
	JOIN employees m ON e.employee_id = m.employee_id 
WHERE
	e.last_name LIKE '%k%';

外连接

应用场景：用于查询一个表中有，一个表中没有

特点

一：外连接的查询结果为主表中的所有记录

		如果从表中有和他匹配的，则显示匹配的值，如果从表中没有和它匹配的，则显示null
		
		外连接查询结果=内连接结果+主表中有而有从表的记录
		
		全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
		
 二：左外连接：left join 左边的是主表
 
 		右外连接：right join 右边的是主表

左外和右外交换两个表的顺序，可以实现同样的效果。

查询没有男朋友的女神名

SELECT
	b.`name`,
	bo.* 
FROM
	beauty b
	LEFT JOIN boys bo ON b.boyfriend_id = bo.id 
WHERE
	bo.id IS NULL;

查询哪个部门没有员工

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

全外连接 mysql不支持全外连接

SELECT
	b.*,
	bo.* 
FROM
	beauty b
	FULL JOIN boys bo ON b.boyfriend_id = bo.id;

交叉连接(笛卡尔乘积)

SELECT
	b.*,
	bo.* 
FROM
	beauty b
	CROSS JOIN boys bo ;

sql99连接查询总结

案例：查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充

SELECT
	b.id '女神编号',
	bo.* 
FROM
	beauty b
	LEFT JOIN boys bo ON b.boyfriend_id = bo.id 
WHERE
	b.id > 3;

查询部门名为SAL或IT的员工信息

SELECT
	d.department_name,
	e.* 
FROM
	departments d
	LEFT JOIN employees e ON d.department_id = e.department_id 
WHERE
	d.department_name IN ( 'SAL', 'IT' );

查询哪个城市没有部门

SELECT
	l.city,
	d.department_name 
FROM
	locations l
	LEFT JOIN departments d ON l.location_id = d.location_id 
WHERE
	d.department_name IS NULL;