Shulou(Shulou.com)06/01 Report--

Editor to share with you how to use JOIN in MySQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Brief introduction

Exclusive to A + public ownership of AB

Exclusive to B + public ownership of AB

Public ownership of AB

Unique to A

B is unique.

Exclusive of A + exclusive of B + public ownership of AB

The uniqueness of A + B

Practice creating tables

Department table

DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` (`dept_ id` int (11) NOT NULL AUTO_INCREMENT, `dept_ name` varchar (30) DEFAULT NULL, `dept_ number`int (11) DEFAULT NULL, PRIMARY KEY (`deptid`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `dept`VALUES ('1depth,' AA', '100'); INSERT INTO `dept`VALUES (' 2efficiency, 'BB',' 200'); INSERT INTO `dept`VALUES ('3cycles,' CC', '300') INSERT INTO `dept` VALUES ('4efficiency,' DD', '400'); INSERT INTO `dept` VALUES (' 5depth, 'HH',' 500')

Employee table

DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` (`emp_ id` int (11) NOT NULL AUTO_INCREMENT, `emp_ name` varchar (30) DEFAULT NULL, `emp_ age`int (11) DEFAULT NULL, `dept_ id` int (11) NOT NULL, PRIMARY KEY (`emp_ id`) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `emp`VALUES ('1mm,' zhangsan', '20mm,' 1'); INSERT INTO `emp`VALUES ('2mm,' lisi', '25th,' 6') INSERT INTO `emp`VALUES ('313,' wangwu','19,'4'); INSERT INTO `emp` VALUES ('414,' zhaoliu','29,'5'); INSERT INTO `emp` VALUES ('513,' xiaohong','30,'2'); INSERT INTO `emp`VALUES ('6, 'xiaohu',' 26,'3'); INSERT INTO `emp` VALUES ('7mm,' zhangle','23,'3') INSERT INTO `emp` VALUES ('8mm,' qingtian', '38th,' 3'); INSERT INTO `emp` VALUES ('9pm,' xiayutian', '36th,' 2'); INSERT INTO `emp` VALUES ('10mm,' fangjia', '40th,' 1'); scenario analysis

1. Left connection (left join)

Exclusive to A + public ownership of AB

SELECT * from emp e LEFT JOIN dept d ON e.dept_id=d.dept_id

two。 Right connection (right join)

Exclusive to B + public ownership of AB

SELECT * from emp e RIGHT JOIN dept d ON e.dept_id=d.dept_id

3. Internal connection (inner join)

Public ownership of AB

SELECT * from emp e INNER JOIN dept d ON e.dept_id=d.dept_id

4. Left outer connection (left join and right table = null)

Unique to A

SELECT * from emp e LEFT JOIN dept d ON e.dept_id=d.dept_id WHERE d.dept_id is null

5. Right outer connection (right join and left table = null)

B is unique.

SELECT * from emp e RIGHT JOIN dept d ON e.dept_id=d.dept_id WHERE e.dept_id is null

6. Fully connected (full outer join)

Exclusive of A + exclusive of B + public ownership of AB

Note: MySQL does not support FULL OUTER JOIN (in ORACLE).

Therefore, using the way of UNION, you can * * merge + remove duplicates * *

Application scenarios:

When the result of the query comes from multiple tables, and the multiple tables are not directly connected, but the information of the query is the same.

Features:

1. The number of query columns of multiple query statements is required to be the same

2. The type and order of each column of a query requiring multiple query statements should be the same.

3. The union keyword * * deduplicates by default, and duplicates can be included if union all is used. * *

SELECT * from emp e LEFT JOIN dept d ON e.dept_id=d.dept_id UNION SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id=d.dept_id

7. Full external connection (full outer join and left and right table = null)

The uniqueness of A + B

SELECT * from emp e LEFT JOIN dept d ON e.dept_id=d.dept_id WHERE d.dept_id is null UNION SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id=d.dept_id WHERE e.dept_id is null

The above is all the contents of the article "how to use JOIN in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.