【MySQL】内连接和外连接自然连接 using

SQL99语法的七种Join

内连接

SQL92语法实现2张表的内连接:

#练习:查询所有的员工的last_name , department_name信息
SELECT employee_id, department_name
FROM employees e,departments d
WHERE e.`department_id`=Id.`department_id`;

SQL99语法实现2张表的内连接:

SELECT last_name , department_name
FROM employees e 
JoIN departments d ON e.`department_id` = d.`department_id`;

SQL99语法实现3张表的内连接:

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_idT` = l.`location_id`;

外连接

MysQL不支持sQL92语法中外连接的写法!支持SQL99

SQL99语法中使用Join ...ON的方式实现多表的查询。这种方式也能解决外连接的问题。MySQL是支持此种方式! SQL99语法如何实现多表的查询。

练习查询所有的员工的last_name , department_name信息

左外连接:
SELECT last_name , department name
FROM employees e  
LEFT OUTER JOIN departments d ON e.`department_id` = d.`department_id `;

#右外连接:
SELECT last_narje , department name
FROM employees e
RIGHT OUTER JOIN departments d ON e.`department_idT = d.`department_id`;

#满外连接: Mysql 不支持满外连接  oracel 支持
SELECT last_narje , department name
FROM employees e
Full OUTER JOIN departments d ON e.`department_idT = d.`department_id`;

Mysql 实现满外连接的效果需要用UNnion,将一下两个图UNion再一起，就实现了满外连接