How to use JOIN in MySQL
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!
