MySQL数据库干货_16—— SQL99标准中的查询

SQL99标准中的查询

MySQL5.7 支持部分的SQL99 标准。

SQL99中的交叉连接(CROSS JOIN)

示例：

使用交叉连接查询 employees 表与 departments 表。

select * from employees cross join departments;

SQL99中的自然连接(NATURAL JOIN)

自然连接

连接只能发生在两个表中有相同名字和数据类型的列上。如果列有相同的名字，但数据类型不同，NATURAL JOIN 语法会引起错误。

自然连接查询

在图片例子中，LOCATIONS 表被用 LOCATION_ID 列连接到 DEPARTMENT表，这是在两个表中唯一名字相同的列。如果存在其它的同名同类型的列，自然连接会使用等值连接的方式连接他们，连接条件的关系为and。

自然连接也可以被写为等值连接：

SELECT d.department_id, d.department_name,

d.location_id , l.city

FROM

departments d , locations l

WHERE

d.location_id = l.location_id;

示例：

使用自然连接查询所有有部门的雇员的名字以及部门名称。

select e.last_name,d.department_name from employees e natural join departments d;

SQL99中的内连接(INNER JOIN)

语法：

• SELECT 查询列表;
• FROM 表1 别名;
• INNER JOIN 连接表(INNER关键字可省略);
• ON 连接条件;

用ON子句指定连接条件

用ON子句指定更多的连接条件

示例:

查询雇员名字为 Fox 的雇员 ID ，薪水与部门名称。

select e.employee_id,e.salary,d.department_name from employees e inner JOIN departments d on e.department_id = d.department_id where e.last_name = 'Fox';

外连接查询(OUTER JOIN)

孤儿数据(Orphan Data)

孤儿数据是指被连接的列的值为空的数据。

左外连接(LEFT OUTER JOIN)

左外连接

左边的表 (EMPLOYEES) 中即使没有与 DEPARTMENTS 表中匹配的行，该查询

也会取回 EMPLOYEES 表中所有的行。

示例：

查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。

select e.last_name,d.department_name from employees e LEFT OUTER JOIN departments d on e.dept_id = d.department_id;

右外连接(RIGTH OUTER JOIN)

右外连接

右边的表 (DEPARTMENTS ) 中即使没有与 EMPLOYEES 表中匹配的行，该查询

也会取回 DEPARTMENTS 表中所有的行。

示例：

查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。

select  e.last_name,d.department_name from  employees  e RIGHT OUTER JOIN  departments  d on e.DEPARTMENT_ID = d.department_id;

全外连接(FULL OUTER JOIN)

注意： MySQL 中不支持 FULL OUTER JOIN 连接

可以使用 union 实现全完连接。

• UNION: 可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了 DISTINCT。
• UNION ALL: 只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据， 那么返回的结果集就会包含重复的数据了。

语法结构

(SELECT  投影列 FROM 表名 LEFT OUTER JOIN  表名 ON  连接条件) 
UNION 
(SELECT  投影列 FROM 表名 RIGHT OUTER JOIN  表名 ON  连接条件)

示例：

查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇 员。

(select e.last_name,d.department_name from employees e LEFT OUTER JOIN departments d on  e.department_id =  d.department_id) 
UNION 
(select  e1.last_name,d1.department_name from employees e1 RIGHT OUTER JOIN departments d1 on d1.department_id = e1.department_id)