文章目录

连接查询的分类Sql92等值连接查询非等值连接查询自连接查询

连接查询的分类

按照年代进行分类

Sql92标准：仅仅支持内连接Sql99标准（推荐）：支持内连接和外连接（左外和右外）以及交叉连接

按照功能进行分类

内连接：等值连接、非等值连接、自连接外连接：左外连接、右外连接、全外连接交叉连接

Sql92

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';

注意：在操作过程中可以选择为表其别名，好处如下：

提高语句的简简洁度区分多个重名的字段 /**查询员工名、工种号和工种名**/ SELECT last_name,e.job_id,job_title FROM employees e,jobs j WHERE d.'job_id'= j.'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个连接条件多表的顺序没有要求一般情况下需要为表起别名可以搭配排序、分组、筛选来进行使用

非等值连接查询

表：job_grade

grade_levellowest_salhighest_salA10002999B30005999C60009999D1000012000 /**查询员工的工资和工资级别**/ 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';

自连接查询

表employees结构如下：

employee_idlast_namemanager_id100K_ing(NULL)101Kochhar100102De Haan100103Hunold102104Ernst103 /**查询员工名和上级名称**/ 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';