等值连接 vs 非等值连接

SELECT last_name,salary ,job_grades.grade_level from employees,job_grades WHERE employees.salary >= job_grades.lowest_sal AND employees.salary < job_grades.highest_sal;

外连接，内连接

左外连接，右外连接 -sql 92语法实现外连接 SELECT a.first_name,a.employee_id,a.department_id FROM employees a ,departments b WHERE a.department_id = b.department_id(+); -mysql不支持sql92语法-

-99语法左外连接

SELECT a.first_name,a.employee_id,a.department_id FROM employees a LEFT OUTER JOIN departments b ON a.department_id = b.department_id; outer可以省略掉 左外连接查出来的是左边的一整张图 右外连接是右边一整张图

union

由于sql中full join不能用到，因此使用union取两个表的并集 union all 两个图a图和b图进行合并，重复部分多余有一份，虽然冗余，但是开发中尽量用union all

中图：内连接 SELECT a.first_name,a.employee_id,a.department_id FROM employees a JOIN departments b ON a.department_id = b.department_id;

左上图 SELECT a.first_name,a.employee_id,a.department_id FROM employees a LEFT JOIN departments b ON a.department_id = b.department_id;

右上图 SELECT a.first_name,a.employee_id,a.department_id FROM employees a RIGHT JOIN departments b ON a.department_id = b.department_id;

左中图 SELECT a.first_name,a.employee_id,a.department_id FROM employees a LEFT JOIN departments b ON a.department_id = b.department_id WHERE a.department_id = NULL;

右中图 SELECT a.first_name,a.employee_id,a.department_id FROM employees a RIGHT JOIN departments b ON a.department_id = b.department_id WHERE b.deparment_id = NULL;

满外连接 左上图 UNION ALL 右中图 SELECT a.first_name,a.employee_id,a.department_id FROM employees a LEFT JOIN departments b ON a.department_id = b.department_id; UNION ALL SELECT a.first_name,a.employee_id,a.department_id FROM employees a RIGHT JOIN departments b ON a.department_id = b.department_id WHERE b.deparment_id = NULL;