Mysql联接查询

     标准的SQl提供多个联接运算，如INNER JOIN（内部联接）、OUTER JOIN（外部联接）、STRAIGHT_JOIN（直接联接）和NATURAL JOIN（自然联接），下面示例通过两个表来说明MYSQL的联接，为简单起见，使用了非范式数据来说明。

-- 表的结构 `colors`
CREATE TABLE IF NOT EXISTS `colors` (
  `color` varchar(50) CHARACTER SET utf8 NOT NULL
) 
-- 表的结构 `flags`
CREATE TABLE IF NOT EXISTS `flags` (
  `color` varchar(50) CHARACTER SET utf8 NOT NULL,
  `country` varchar(50) CHARACTER SET utf8 NOT NULL
)
-- 导出表中的数据 `colors`
INSERT INTO `colors` (`color`) VALUES ('red'),('white'),('blue'),('green'),('black');
-- 导出表中的数据 `flags`
INSERT INTO `flags` (`color`, `country`) VALUES ('red', 'USA'),('white', 'USA'),('blue', 'USA'),('blue', 'Sweden'),('yellow', 'Sweden');

colors表：                            flags表：

            

1、INNER JOIN（内部联接）

检索flags表和colors表中共同存在的颜色

select f.* from `flags` as f inner join colors as c  on f.color=c.color;    -- ON子句
select f.* from `flags` as f inner join colors as c using (color);    -- USING子句
select f.* from `flags` as f inner join colors as c  where f.color=c.color ;    -- WHERE子句
select f.* from `flags` as f cross join colors as c using(color);    -- 交叉联接

表别名Alias：MYSQL中表的别名最大长度为256个字符，而表明最大长度仅为64个字符

USING子句：当两个表含有相同列时，可以使用USING子句简化ON语法

WHERE字句：利用相联接的列以及检索数据的约束条件减少了代码的可读性，且多表查询时容易遗漏

2、OUTER JOIN（外部联接）

检索flags表中存在但在colors表中不存在的颜色

select f.* from flags as f left outer join colors as c using (color) where c.color is null; -- 左联接
select f.* from colors as c right outer join flags as f using (color) where c.color is null; -- 右联接

检索colors表中存在但在flags中不存在的颜色

select c.* from colors as c left outer join flags as f using (color) where f.color is null; -- 左联接
select c.* from flags as f right outer join colors as c using (color) where f.country is null; -- 右联接

上述的SQL语句不仅包含OUTER JOIN语法，还包括关键字LEFT，OUTER其实是可选关键字，通常仅用LEFT JOIN简化SQL语法。

主要使用OUTER JOIN 的原因：

    1：数据值集合位置时，仍需要检索所有能够匹配部分约束条件的集合

    2：当规范化数据库并没有强制参照完整性时（碎片），需要使用OUTER JOIN。

    在上面示例中，逻辑上是成立的，但我们绝对的约束表结构时，既flags中的颜色一定在colors表中存在，以上的检索结果就是所谓的碎片

3、STRAIGHT_JOIN（直接联接）

select f.* from `flags` as f STRAIGHT_JOIN  colors as c;    -- 直接联接
select f.* from `flags` as f join colors as c using(color);    -- JOIN联接

直接联接的结果将是多表间的笛卡尔积，作为MYSQL优化器的提示，能过按给定的顺序确定读入的表

4、NATURAL JOIN（自然联接）

select f.* from flags as f natural join colors as c

自然联接是一种等价联接，它将具有相同名称的列自动进行记录匹配，NATURAL[LEFT|RIGHT] JOIN 子句的作用相当于[INNER|LEFT|RIGHT] JOIN子句，使用自然联接有一定的风险，必须指定具体的联接列，否则在扩充表时会造成较大的查询结果。

5、UPDATE和DELETE JOIN 语法

在MYSQL中，联接不仅局限于SELECT语句，还支持联接更新和删除

update flags as f inner join colors as c using(color) set f.color = upper(color), c.color = upper(color);

       

delete  from flags,colors using flags,colors where colors.color = flags.color and flags.color = 'BLUE'; 

       

6、复杂联接

检索所有国旗同时含有red、white、 blue三种颜色的国家

select f1.country from flags as f 
inner join flags as f1 using (country)
inner join flags as f2 using (country)
where f.color='red' and f1.color='white' and f2.color='blue';

7、合并查询（UNION）

检索含有red、blue颜色的国家

select f.* from flags as f inner join colors as c using(color) where f.color = 'red' union select f.* from flags as f inner join colors as c using(color) where f.color = 'blue';   -- 检索去除相互重复的数据行
select f.* from flags as f inner join colors as c using(color) where f.color = 'red' union all select f.* from flags as f inner join colors as c using(color) where f.color = 'blue'; -- 检索所有匹配的数据行
select f.* from flags as f inner join colors as c using(color) where f.color = 'red' union distinct select f.* from flags as f inner join colors as c using(color) where f.color = 'blue';-- 检索唯一的数据行