mysql-多表连接的学习

-- 4.3
-- ****************************************************************************************
1. 多表连接
1.1 为了避免笛卡尔积，可以在where中加入有效的连接条件
        SELECT table1.column,table2.column 
        FROM table1,table2
        WHERE table1.column1=table2.column2; #连接条件
        -- 在表中有相同列时，在列名之前加上表名前缀
1.2 多表查询分类讲解
分类1 等值连接VS 非等值连接 
         select e.last_name,d.department_name,l.city
         from employees e ,departments d,locations l
         WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`
         总结：连接n个表，至少需要n-1个连接条件
 分类2 自连接VS非自连接
            table1和table2 本质上是同一张表，只是取别名的方式虚拟成两张表用以表达不同的意义，然后两个表可以进行内连接，外连接等查询
            SELECT CONCAT(worker.last_name,' works for ',manager.last_name)
            FROM employees worker, employees manager
            WHERE worker.`last_name`=manager.`last_name`
            练习：查询出last_name为 ‘Chen’ 的员工的 manager 的信息。
            SELECT manager.*
            FROM employees worker, employees manager
            WHERE worker.manager_id = manager.employee_id and worker.`last_name`='chen'
            -- 内连接：合并具有同一列的两个以上的表的行，结果集中不含一个表和另一个表不匹配的行
            SELECT employee_id, department_name
            FROM employees e,departments d 
            WHERE e.`department_id` = d.`department_id`
            #SQL99语法实现内连接：
            SELECT table1.column, table2.column,table3.column
            FROM table1
            JOIN table2 ON table1和table2的连接条件
            JOIN table3 ON table2和table3的连接条件
            练习：
            SELECT last_name,department_name
            FROM employees e INNER JOIN departments d
            ON e.`department_id` = d.`department_id`;

            SELECT last_name,department_name,city
            FROM employees e JOIN departments d
            ON e.`department_id` = d.`department_id`
            JOIN locations l
            ON d.`location_id` = l.`location_id`;
            #SQL99语法实现内连接：
            -- 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
            -- 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
            SELECT last_name, department_name
            FROM employees LEFT JOIN departments
            ON employees.department_id=departments.department_id -- 包含了一个人是没有部门名称的
            -- 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。
            SELECT last_name, department_name
            FROM employees RIGHT JOIN departments
            ON employees.department_id=departments.department_id  -- departments是主表，很多部门没有是没有人的（last_name=null）
            -- union 的使用
            SELECT COLUMN ,....from table1
            union ALL
            SELECT column,......FROM table2 
            
            中间： 
            SELECT employee_id,last_name,department_name
            FROM employees JOIN departments
            ON employees.`department_id`=departments.`department_id`
            
            左上：
            SELECT employee_id,last_name,department_name
            FROM employees left JOIN departments
            ON employees.`department_id`=departments.`department_id`
            
            右上：
            SELECT employee_id,last_name,department_name
            FROM employees right JOIN departments
            ON employees.`department_id`=departments.`department_id`
            
            左中：
            SELECT employee_id,last_name,department_name
            FROM employees left JOIN departments
            ON employees.`department_id`=departments.`department_id`
            WHERE departments.`department_name` is NULL 
            
            右中： 
            SELECT employee_id,last_name,department_name
            FROM employees right join departments
            ON employees.`department_id`=departments.`department_id`
            WHERE employees.`employee_id` is NULL
            
            左下：
            左上+右中
            SELECT employee_id,last_name,department_name
            FROM employees left JOIN departments
            ON employees.`department_id`=departments.`department_id`
            UNION ALL
            SELECT employee_id,last_name,department_name
            FROM employees right join departments
            ON employees.`department_id`=departments.`department_id`
            WHERE employees.`employee_id` is NULL
            
            右上+左中：
            SELECT employee_id,last_name,department_name
            FROM employees right JOIN departments
            ON employees.`department_id`=departments.`department_id`
            UNION ALL
            SELECT employee_id,last_name,department_name
            FROM employees left JOIN departments
            ON employees.`department_id`=departments.`department_id`
            WHERE departments.`department_name` is NULL 
            
            下中：
            左中+右中
            SELECT employee_id,last_name,department_name
            FROM employees left JOIN departments
            ON employees.`department_id`=departments.`department_id`
            WHERE departments.`department_name` is NULL 
          union ALL
            SELECT employee_id,last_name,department_name
            FROM employees right join departments
            ON employees.`department_id`=departments.`department_id`
            WHERE employees.`employee_id` is NULL