【MySQL笔记】七种JOIN的SQL

摘要：问题回答个数标签标签名称是什么是什么前端是什么大前端是什么是什么是什么多线程七全连接去交集问题回答个数标签标签名称是什么是什么是什么多线程

以一个简易问答系统为例，包括问题表和问题所属标签，问题表如下：

CREATE TABLE `t_qa` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL DEFAULT "" COMMENT "标题",
  `answer_count` int(5) unsigned NOT NULL DEFAULT "0" COMMENT "回答个数",
  `label_id` bigint(20) unsigned NOT NULL DEFAULT "0" COMMENT "标签id",
  `create_by` bigint(20) unsigned NOT NULL DEFAULT "0" COMMENT "创建人",
  `create_date` datetime NOT NULL DEFAULT "0000-00-00 00:00:00" COMMENT "创建时间",
  `update_by` bigint(20) unsigned DEFAULT NULL COMMENT "更新人",
  `update_date` datetime DEFAULT NULL COMMENT "更新时间",
  `del_flag` tinyint(1) unsigned NOT NULL DEFAULT "0" COMMENT "0：不删除，1：删除",
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_qa` (`id`, `title`, `answer_count`, `label_id`, `create_by`, `create_date`, `update_by`, `update_date`, `del_flag`)
VALUES
    (1, "Java是什么？", 5, 1, 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (2, "PHP是什么？", 4, 2, 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (3, "前端是什么？", 3, 3, 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (4, "nodejs是什么？", 2, 0, 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (5, "css是什么？", 1, 0, 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (6, "JavaScript是什么？", 0, 0, 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0);

标签表如下：

CREATE TABLE `t_label` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT "" COMMENT "名称",
  `create_by` bigint(20) unsigned NOT NULL DEFAULT "0" COMMENT "创建人",
  `create_date` datetime NOT NULL DEFAULT "0000-00-00 00:00:00" COMMENT "创建时间",
  `update_by` bigint(20) unsigned DEFAULT NULL COMMENT "更新人",
  `update_date` datetime DEFAULT NULL COMMENT "更新时间",
  `del_flag` tinyint(1) unsigned NOT NULL DEFAULT "0" COMMENT "0：不删除，1：删除",
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_label` (`id`, `name`, `create_by`, `create_date`, `update_by`, `update_date`, `del_flag`)
VALUES
    (1, "java", 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (2, "php", 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (3, "大前端", 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (4, "mybatis", 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (5, "python", 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0),
    (6, "多线程", 0, "2017-08-24 17:43:53", 0, "2017-08-24 17:43:53", 0);

SELECT 
    tq.title, tq.answer_count, tl.id, tl.name
FROM 
    t_qa tq LEFT JOIN t_label tl ON tq.label_id = tl.id

SELECT 
    tq.title, tq.answer_count, tl.id, tl.name
FROM 
    t_qa tq RIGHT JOIN t_label tl ON tq.label_id = tl.id

SELECT 
    tq.title, tq.answer_count, tl.id, tl.name
FROM 
    t_qa tq INNER JOIN t_label tl ON tq.label_id = tl.id

SELECT 
    tq.title, tq.answer_count, tl.id, tl.name
FROM 
    t_qa tq LEFT JOIN t_label tl ON tq.label_id = tl.id
WHERE
    tl.id IS NULL

SELECT 
    tq.title, tq.answer_count, tl.id, tl.name
FROM 
    t_qa tq RIGHT JOIN t_label tl ON tq.label_id = tl.id
WHERE
    tq.label_id IS NULL

由于MySQL不支持FULL OUTER JOIN，所以如果有全连接需求时，可用表达式：full outer join = left outer join UNION right outer join来实现。

SELECT tq.title, tq.answer_count, tl.id, tl.name FROM t_qa tq LEFT JOIN t_label tl ON tq.label_id = tl.id
UNION
SELECT tq.title, tq.answer_count, tl.id, tl.name FROM t_qa tq RIGHT JOIN t_label tl ON tq.label_id = tl.id 

SELECT tq.title, tq.answer_count, tl.id, tl.name FROM t_qa tq LEFT JOIN t_label tl ON tq.label_id = tl.id WHERE tl.id IS NULL
UNION
SELECT tq.title, tq.answer_count, tl.id, tl.name FROM t_qa tq RIGHT JOIN t_label tl ON tq.label_id = tl.id WHERE tq.label_id IS NULL