mysql的七种基本关联查询方式

 

 

 

-- 内连接
SELECT last_name , department_name FROM employees e JOIN departments d
ON e.department_id = d.department_id

-- 左外连接
SELECT last_name , department_name FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id

-- 右外连接
SELECT last_name , department_name FROM employees e RIGHT  JOIN departments d
ON e.department_id = d.department_id

-- 左滤连接
SELECT last_name , department_name FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id WHERE d.department_id IS NULL 

-- 右滤连接
SELECT last_name , department_name FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id WHERE e.department_id is null

-- 全连接
SELECT last_name , department_name FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL 
SELECT last_name , department_name FROM employees e RIGHT  JOIN departments d
ON e.department_id = d.department_id WHERE  e.department_id is null


-- 反内连接
SELECT last_name , department_name FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id WHERE d.department_id IS NULL 
UNION ALL 
SELECT last_name , department_name FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id WHERE e.department_id is NULL