直接连接会产生笛卡儿积
连接查询的分类

  • 内连接：等值连接、非等值连接、自连接        <---- Sql92标准仅支持内连接
  • 外连接：左外连接、右外连接、全外连接                Sql99标准全支持
  • 交叉连接

一、Sql92：仅支持内连接（要思考连接后的表的形态）
（一）等值连接  

/**案例1：查询女神名和对应的男神名**/
SELECT name,boyName     #不同表的两个字段
FROM beauty,boys        #需连接的两个表
WHERE beauty.boyfriend_id = boys.id;
/**案例2：查询员工名对应的部门名**/
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
/**案例3：查询员工名、工种号和工种名**/
SELECT last_name,employees.job_id,job_title
FROM employees,jobs
WHERE employees.job_id = jobs.job_id;
 
1. 添加筛选的等值查询
/**查询有奖金的员工名、部门名**/
SELECT last_name,department_name
FROM employeess e,departments d    #加别名很好
WHERE e.department_id = d.department_id 
AND e.commission_pct IS NOT NULL;
/**查询城市名中第二个字符为o的城市名和部门名**/
SELECT city, department_name
FROM department d,location l
WHERE d.location_id = l.location_id
AND city LIKE '_O%';
 
2. 添加分组的等值查询
/**查询每个城市的部门个数**/
SELECT COUNT(*) 部门个数,city
FROM location l,department t
WHERE l.location_id = t.location_id
GROUP BY city
/**查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资**/
SELECT department_name,d.manager_id,MIN(salary)
FROM department d, employees e
WHERE d.department_id = e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name;
 
3.添加排序的等值查询
/**查询每个工种的工种名和员工的个数，并且按照员工的个数降序排序**/
SELECT job_title,COUNT(*) 员工个数
FROM job j,employees e
WHERE j.job_id = e.job_id
GROUP BY job_title
ORDER BY 员工个数 DESC;
4.三表连接的等值查询
/**查询员工名、部门名和所在的城市**/
SELECT employee_name,department_name,city
FROM employees e, departement d,location l 
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

　　总结：

查询结果为多个表的交集部分 n表链接，至少需要n-1个连接条件
多表的顺序没有要求 一般情况下需要为表起别名

（二）非等值连接

/**查询员工的工资和工资级别**/
SELECT salary,grade_level
FROM employees e, job_grade jg
WHERE salary BETWEEN lowest_sal AND highest_sal
/**查询员工的工资和工资级别为A的工资**/
SELECT salary,grade_level
FROM employees e, job_grade jg
WHERE salary BETWEEN lowest_sal AND highest_sal
AND grade_level = 'A';

（三）自连接

/**查询员工名和上级名称**/
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e, employees m
WHERE e.employee_id = m.manager_id;