等值连接
备注
1. 结果集为多表的交集部分
2. n表连接,至少需要n-1个连接条件
3. 表的顺序没有要求
示例
查询有奖金的员工名,部门名
SELECT name, dept_name, bonus
FROM emp, dept
WHERE emp.id = dept.id
AND emp.bonus ISNOTNULL;
查询每个城市的部门个数
SELECTCOUNT(*), city
FROM dept AS d, location AS l
WHERE d.loc_id = l.id
GROUPBY city
查询每个工种的工种名和员工数,按员工数降序排序
SELECTCOUNT(*), job_name
FROM emp, job
WHERE emp.job_id = job.id
ORDERBYCOUNT(*) DESC;
查询员工名,部门名和部门所在城市
SELECT emp.name, dept.name, city
FROM emp, dept, location
WHERE emp.dept_id = dept.id
AND dept.loc_id = location.id
非等值连接
示例
查询员工的工资和工资级别
SELECT salary, grade_level
FROM emp, salary_grade g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;
自连接
示例
查询员工名及其上级名
SELECT e.name, m.name manager
FROM emp e, emp m
WHERE e.manager_id = m.id;
