13、sql99语法的连接查询

sql99语法

SELECT 查询列表
FROM 表1 别名
[连接类型] JOIN 表2 别名
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[ORDER BY 排序条件]

一、内连接

语法：

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件

1、等值连接

案例一、查询员工名、部门名

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

案例二、查询名字中包含e的员工名和工种名

SELECT E.last_name, J.job_title
FROM employees AS E
INNER JOIN jobs AS J
ON E.job_id = J.job_id
WHERE E.last_name LiKE "%e%";

案例三、查询部门个数>3的城市名和部门个数

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

案例四、查询员工个数大于3的部门名和员工个数，并按个数降序

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

案例五：查询员工名、部门名、工种名，并按部门名降序

SELECT E.last_name, D.department_name, J.job_title
FROM employees AS E
INNER JOIN departments AS D ON E.department_id = D.department_id
INNER JOIN jobs AS J ON E.job_id = J.job_id
ORDER BY D.department_name DESC;

2、非等值连接

案例一、查询员工的工资级别

SELECT E.salary, J.grade_level
FROM employees AS E
INNER JOIN job_grade AS J
ON E.salary BETWEEN J.lowest_sal AND J.highest_sal;

案例二、查询每个工资级别的个数>2的个数，并且按工资级别降序

SELECT J.grade_level, COUNT(*)
FROM employees AS E
INNER JOIN job_grades AS J
ON E.salary BETWEEN J.lowest_sal AND J.highest_sal
GROUP BY J.grade_level
HAVING COUNT(*) > 2
ORDER BY grade_level DESC;

3、自连接

查询姓名中包含k的员工的名字、编号，上级的名字、编号

SELECT E.last_name, E.employee_id, M.last_name, M.employee_id
FROM employees AS E
INNER JOIN employees AS M
ON E.manager_id = M.employee_id
WHERE E.last_name LIKE "%k%";

二、外连接

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

特点：

• 外连接的查询结果为主表中的所有记录
  如果从表中有和它匹配的，则显示匹配的值
  如果从表中没有和它配的，则显示为null

• 左外连接：LEFT JOIN 左边的是主表
  右外连接：RIGHT JOIN 右边的是主表

1、左外、右外连接

案例一：查询没有男朋友的女神名

SELECT B.name, Bo.*
FROM girls.beauty AS B
LEFT OUTER JOIN girls.boys AS Bo
ON B.boyfriend_id = Bo.id
WHERE Bo.id IS NULL;

SELECT B.name, Bo.*
FROM girls.boys AS Bo
RIGHT OUTER JOIN girls.beauty AS B
ON B.boyfriend_id = B.id
WHERE B.id IS NULL;

案例二：查询哪个部门没有员工

SELECT D.department_name
FROM employees AS E
RIGHT OUTER JOIN departments AS D
ON E.department_id = D.department_id
WHERE E.department_id IS NULL;

SELECT D.department_name
FROM departments AS D
LEFT OUTER JOIN employees AS E
ON E.department_id = D.department_id
WHERE E.department_id IS NULL;

2、全连接

SELECT Bo.*, B.*
From girls.beauty AS B
FULL OUTER JOIN girls.boys AS Bo
ON B.boyfriend_id = Bo.id;

3、交叉连接

SELECT B.*, Bo.*
FROM girls.beauty AS B
CROSS JOIN girls.boys AS Bo;

练习题

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

SELECT B.name, Bo.*
FROM girls.beauty AS B
LEFT OUTER JOIN girls.boys AS Bo
ON B.boyfriend_id = Bo.id
WHERE B.id > 3;

2、查询哪个城市没有部门

SELECT L.city
FROM departments AS D
RIGHT OUTER JOIN locatiosn AS L
ON D.location_id = L.location_id
WHERE D.department_id IS NULL;

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

SELECT E.*
FROM employees AS E
RIGHT OUTER JOIN departments AS D
ON E.department_id = D.department_id
WHERE D.department_name IN ("SAL", "IT");