chapter10_外联接、自联接与联合

• 左外连接 LEFT OUTER JOIN

  左外联接接收左表的所有行，并用这些行与右表中的行进行匹配。 如果没有相符的结果就在右表中的列中填入NULL

  示例

    SELECT g.girl, t.toy FROM girls AS g LEFT OUTER JOIN toys AS t ON g.toy_id = t.toy_id;
  

  当左表和右表具有一对多的关系时，左外联接特别有用

• 右外连接 RIGHT OUTER JOIN

  右外联接接收右表的所有行，并用这些行与左表中的行进行匹配。 如果没有相符的结果就在左表中的列中填入NULL

• 一般要固定习惯，只使用左外联接和右外联接的一种，而更改表的顺序

• 自引用外键：外键的来源是同一张的表的主键

  示例

    CREATE TABLE `table` (
        `num1` int(4) NOT NULL AUTO_INCREMENT,
        `num2` int(4) DEFAULT NULL,
        PRIMARY KEY (`num1`),
        CONSTRAINT `fk` FOREIGN KEY (`num2`) REFERENCES `table` (`num1`)
    );
  

• 自联接 INNER JOIN(内连接用的也是INNER JOIN)

  (1) 把单一表当成两张具有完全相同信息的表进行查询

  (2) 不添加任何限制条件的INNER JOIN效果和笛卡尔积相同

  (3) 示例

    SELECT c1.name, c2.name AS boss FROM clown_info AS c1 INNER JOIN clown_info AS c2 ON c1.boss_id = c2.id;
  

• UNION:联合多个SELECT查询的结果

  (1) 示例

        table1                  table2
    --------------          --------------
     num1  | num2            num1  | num2
    --------------          --------------
      0    |   1              1    |   2
    --------------          --------------
      1    |   2              2    |   3
    --------------          --------------
      2    |   3              3    |   4
    --------------          --------------
  
    SELECT num1 AS num FROM table1 
    UNION
    SELECT num2 AS num FROM table2
    ORDER by num DESC;
  
    结果
  
         num
      ---------
          4
      ---------
          3
      ---------
          2
      ---------
          1
      ---------
          0
      ---------
  

  (2) 使用UNION时，ORDER BY 只能添加到最后，且只能使用一次，不能每个SELECT都用

  (3) 使用UNION时，每个SELECT语句中的__列的数量必须一致__

  (4) 使用UNION时，每个SELECT语句中包含的表达式与统计函数也必须相同

  (5) 使用UNION时，列的数据类型必须相同或可以互相转换

  (6) UNION会自动消除重复数据

  (7) 如果希望看到重复数据，要使用__UNION ALL__

  上面的示例使用UNION ALL

    SELECT num1 AS num FROM table1 
    UNION ALL
    SELECT num2 AS num FROM table2
    ORDER by num DESC;
  

  结果

         num
      ---------
          4
      ---------
          3
      ---------
          2
      ---------
          2
      ---------
          1
      ---------
          0
      ---------
  

  (8) 从联合的结果可以创建新表

  示例

    CREATE TABLE my_union 
    AS
    SELECT title FROM job_current 
    UNION
    SELECT title FROM job_desired
    UNION
    SELECT title FROM job_listings;
  

• 联接与子查询的比较

  (1) 几乎所有能使用子查询办到的事情都能用联接实现

  (2) 联接绝大多数情况下比子查询快

  (3) 结果集需要来自多张表的时候，联接是比较好的选择；子查询不能返回多列

  (4) 子查询可以使用__统计函数__

  (5) 子查询还可以与UPDATE, INSERT, DELETE连用