Shulou(Shulou.com)05/31 Report--

This article will explain in detail how the SQL of seven kinds of JOIN in MySQL is. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Prepare data

Take a simple question and answer system as an example, including the question list and the label to which the question belongs. The question list is as follows:

CREATE TABLE `tqa` (`id`bigint (20) NOT NULL AUTO_INCREMENT, `title`NOT NULL DEFAULT''COMMENT' title', `answer_ count`int (5) unsigned NOT NULL DEFAULT'0' COMMENT 'answer', `label_ id`bigint (20) unsigned NOT NULL DEFAULT'0' COMMENT 'label id', `create_ by`bigint (20) unsigned NOT NULL DEFAULT' 0' COMMENT 'founder', `create_ date`datetime NOT NULL DEFAULT '0000-00-0000: 00Rod 00' COMMENT' creation time' `update_ by` bigint (20) unsigned DEFAULT NULL COMMENT 'update person', `del_ date`datetime DEFAULT NULL COMMENT 'update time', `del_ date` tinyint (1) unsigned NOT NULL DEFAULT'0' COMMENT'0: do not delete 1: delete', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `tqa` (`id`, `title`, `answer_ count`, `label_ id`, `create_ by`, `create_ date`, `update_ by`, `update_ date`, `del_ date`) VALUES (1, 'what is Java?' , 5, 1, 0, '2017-08-24 17 4315 53, 0,' 2017-08-24 17 14 14 43 15 15, 0), (2, 'what is PHP?' , 4, 2, 0, '2017-08-24 17 4315 53, 0,' 2017-08-24 17 14 14 43 15 15, 0), (3, 'what is the front end?' , 3, 3, 0, '2017-08-24 17 4315 53, 0,' 2017-08-24 17 14 14 43 15 15, 0), (4, 'what is nodejs?' , 2, 0, 0, '2017-08-24 17 4315 53, 0,' 2017-08-24 17 14 14 43 15 15, 0), (5, 'what is css?' , 1, 0, 0, '2017-08-24 17 4315 53, 0,' 2017-08-24 17 14 14 43 15 15, 0), (6, 'what is JavaScript?' , 0, 0, 0, '2017-08-24 17 4315 53, 0,' 2017-08-24 17 14 14 43 15, 0)

The label table is as follows:

CREATE TABLE `tlabel` (`id` bigint (20) NOT NULL AUTO_INCREMENT, `name` varchar (50) NOT NULL DEFAULT''COMMENT' name', `create_ by` bigint (20) unsigned NOT NULL DEFAULT'0' COMMENT 'founder', `create_ date`datetime NOT NULL DEFAULT '0000-00-0000: 0000' COMMENT' creation time, `update_ by` bigint (20) unsigned DEFAULT NULL COMMENT 'update person', `update_ date`update' `del_ room`tinyint (1) unsigned NOT NULL DEFAULT'0' COMMENT'0: do not delete 1: delete', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `tlabel` (`id`, `name`, `update_ by`, `create_ date`, `update_ by`, `update_ date`, `del_ plac`) VALUES (1, 'java', 0,' 2017-08-24 1743 create_ 53, 0, '2017-08-24 1743 create_ 53, 0), (2,' php', 0, '2017-08-24 17 43 create_ 53, 0), (3) 'big front end', 0, '2017-08-24 17 43 53, 0,' 2017-08-24 17 43 V 53 V, 0), (4, 'mybatis', 0,' 2017-08-24 17 43 V 53 V, 0, '2017-08-24 17 14 14 14 15 43 V 53 V, 0), (5,' 08-24 17 14 14 14 4 4 4 14 14 14 4 4 4 14 14 4 4 4 14 14 43 d 53 V), 0, '2017-08-24 17 14 43 d 53 0), (6, 'multithreaded', 0, '2017-08-24 17-4315-53, 0,' 2017-08-24 17-14-43-43-53)

1. Left connection (LEFT JOIN)

What is the number of questions answered by the tag id tag name Java? What is 51javaPHP? What is the front end of 42php? What is the 33 big front-end nodejs? What is 2NULLNULLcss? What is 1NULLNULLJavaScript? 1NULLNULLSELECT tq.title, tq.answer_count, tl.id, tl.name FROM t_qa tq LEFT JOIN t_label tl ON tq.label_id = tl.id

Second, right connection (RIGHT JOIN)

What is the number of questions answered by the tag id tag name Java? What is 51javaPHP? What is the front end of 42php? 33 large frontend NULLNULL4mybatisNULLNULL5pythonNULLNULL6 multithreaded 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

3. Internal connection (INNER JOIN)

What is the number of questions answered by the tag id tag name Java? What is 51javaPHP? What is the front end of 42php? 33 large frontend 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

4. Left-only connection (LEFT JOIN)

What is the number of questions answered by the tag id tag name nodejs? What is 2NULLNULLcss? What is 1NULLNULLJavaScript? 0NULLNULLSELECT 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

5. Right unique connection (RIGHT JOIN)

Number of questions answered: id tag name NULLNULL4mybatisNULLNULL5pythonNULLNULL6 multithreaded 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

VI. Fully connected (FULL JOIN)

Because MySQL does not support FULL OUTER JOIN, if there is a full connection requirement, you can use the expression: full outer join = left outer join UNION right outer join to implement it.

What is the number of questions answered by the tag id tag name Java? What is 51javaPHP? What is the front end of 42php? What is the 33 big front-end nodejs? What is 2NULLNULLcss? What is 1NULLNULLJavaScript? 0NULLNULLNULLNULL4mybatisNULLNULL5pythonNULLNULL6 multithreaded 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

7. Fully connected de-intersection (FULL JOIN)

What is the number of questions answered by the tag id tag name nodejs? What is 2NULLNULLcss? What is 1NULLNULLJavaScript? 0NULLNULLNULLNULL4mybatisNULLNULL5pythonNULLNULL6 multithreaded 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 about how the SQL of the seven JOIN in MySQL is shared here. I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.