-- 多表查询SELECT employee_id,department_name
FROM employees,departments
#1.两个表的连接条件WHERE employees.`department_id`=departments.`department_id`;#2.如果查询语句中出现了多个表中都存在的字段,则必须指明此字段所在的表:SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;#从sql优化的角度,建议多表查询时,每个字段前都指明其所在的表#3.可以给表起别名,缩短代码长度,设置了别名必须使用,不能再用原名SELECT emp.employee_id,dep.department_name,emp.department_id
FROM employees emp,departments dep
WHERE emp.`department_id`=dep.`department_id`;
#查询员工的employee_id,lastname,department_name,citySELECT emp.employee_id,emp.last_name,dep.department_name,loc.city
FROM employees emp,departments dep,locations loc
WHERE emp.`department_id`=dep.`department_id`AND dep.`location_id`=loc.`location_id`;#有n个表实现多表查询,则需要至少n-1个连接条件
多表查询的分类
-- 多表查询的分类-- 1.等值连接 非等值连接:SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
#where e.`salary` between j.`lowest_sal` and j.`highest_sal`; #查询薪资的等级,在某区间内WHERE e.`salary`>=j.`lowest_sal`AND e.`salary`<=j.`highest_sal`;-- 2.自连接: 非自连接#查询员工id,员工姓名,及其管理者的id和姓名SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.`manager_id`=mgr.`employee_id`;#员工在员工表,管理者也在员工表-- 3.内连接 外连接#外连接:左外连接,右外连接,满外连接#SQL92语法实现外连接:使用+ ----------mysql不支持!SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id`