【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再一起，就实现了满外连接

 

 

SQL99语法的新特性1:自然连接

 

 

SQL99语法的新特性2:USING

 

 

你能看出与自然连接NATURALJOIN 不同的是，USING指定了具体的相同的字段名称，你需要在USING的括号()中填入要指定的同名字段。同时使用JOIN...USING可以简化JOIN ON的等值连接。它与下面的SQL查询结果是相同的: