数据库的内外链接和左右链接

@[toc]

数据库连接

准备数据

• 标题表（title）

DROP TABLE IF EXISTS `title`;
CREATE TABLE `title` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `title` VALUES (1,'孙中明'),(2,'公众号'),(3,'大数据'),(4,'数据库');

• 内容表（article）

DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `article` VALUES (1,'孙中明是一个公众号'),(2,'公众号是一个平台'),(3,'大数据是一个技术'),(5,'Java 是一种语言');

内连接（inner join）

用比较运算符根据每个表共有的列的值匹配两个表中的行（=或>、<）也叫等值连接，得到的满足条件的A和B表内部的数据（必须两边都满足才查出）。

select * from  article 
         inner join title  on  title.id = article.id 

这个是交集，4 和 5 没有对应的所以没有出来。

如果不添加 ON条件约束的话，取得的是表的笛卡尔积。在添加了ON条件约束后，获取的是同时符合ON条件的 A表和B表数据。

• 与where的区别，

  where 子句中过滤的条件记录都不会加入到最后的结果集；

  on字句如果过滤的条件的数据任然会加入；被驱动的表没有的字段被当做null和驱动表一起加入到结果集里面

  但是如果on自己放在内连接中那样的话就和where的作用是等价的。

外连接（outer join）

外连接之左连接（left join）

首先是左表数据全部罗列，然后有满足条件的右表数据都会全部罗列出。若两条右表数据对左表一条数据，则会用对应好的左表数据补足作为一条记录。

以左表为驱动表，取得左表全部的数据。
然后右表满足条件的数据会对应在左表数据后面，作为添加的外部行列出。

如果没有满足左表的数据，则会用NULL全部填充到外部行。

如果有多条满足左表数据，那么会用左表对应的数据补足，按行逐条对应右表数据。（也就是左表数据重复生成，右表的每条记录都生成一行，然后对应同一个左表数据）

SELECT
    * 
FROM
    title
    left JOIN article ON title.id = article.id

查询数据如下：

left join是left outer join的缩写，所以作用是一样的。

left join + where B.column is null, 查出和两张表中没有关联的数据。

SELECT
    * 
FROM
    title
    left JOIN article ON title.id = article.id
    where article.id is null

查询数据如下：

外连接之右连接（right join）

跟左连接相反，会以右表为驱动表。与左连恰恰相反，首先是右表数据全部罗列，然后有满足条件的左表数据都会全部罗列出。若两条左表数据对右表一条数据，则会用对应好的右表数据补足作为一条记录。

SELECT
    * 
FROM
    title
    right  JOIN article ON title.id = article.id

查询数据如下：

外连接之全外连接(full join )

full join （mysql不支持，但是可以用 left join union right join代替）

SELECT
    * 
FROM
    title
    left  JOIN article ON title.id = article.id
    
    union
    
    SELECT
    * 
FROM
    title
    right  JOIN article ON title.id = article.id
    

因为只要满足 union上面的sql或者下面的sql一种情况就ok了。 如下我们可以升级,查询出两种表中没有关系的数据：

SELECT
    * 
FROM
    title
    left  JOIN article ON title.id = article.id
    
    where article.id is null
    
    union
    
    SELECT
    * 
FROM
    title
    right  JOIN article ON title.id = article.id
    where title.id is null
    

查询数据如下：

交叉连接(cross join)

交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。

显式的交叉连接：A，B表记录的排列组合，即笛卡儿积。

将查询的结果和里那个一个表的结果相互匹配的组合。

select * from  school.student  stu
         cross join school.score
         order by stu.id

可以看到， 左边的表4条数据， 每一条对应右边的4条数据， 4x4=16条数据。

SELECT
    * 
FROM
    title
    CROSS JOIN article 
WHERE
    title.id = article.id 
ORDER BY
    title.id

转成内连接，查询如下数据：

UNION

超大型数据尽可能尽力不要写子查询，使用连接（JOIN）去替换它，使用联合(UNION)来代替手动创建的临时表。

UNION是会把结果排序，union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中（即把两次或多次查询结果合并起来。）。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。

要求

两次查询的列数必须一致

推荐

列的类型可以不一样，但推荐查询的每一列，想对应的类型以一样

可以来自多张表的数据

多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。

如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。如果不想去掉重复的行，可以使用union all。

如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

SELECT
    * 
FROM
    title

UNION

SELECT
    * 
FROM
    article

1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名。

2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

UNION ALL

默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。