〖Python database development combat – MySQL chapter ㉑〗 – outer join of data table
|🎉 The faint thunder and the hazy sky. 🎉|
|🎉 But I hope the wind and rain come, I can keep you here. 🎉|
✌ About the author: Husky thirsting for power✌, you can call me 🐶 Husky 🐶 , a blogger dedicated to TFS – full stack empowerment ✌
🏆 CSDN blog expert certification, MVP of the third season of the Nova Project full stack track , Huawei Cloud Sharing experts, Alibaba Cloud expert bloggers 🏆
📫 If there are mistakes in the knowledge points of the article, please correct me! Learn and progress together with everyone👀
💬 Life motto: Being superior to others is not noble. The real nobility should be superior to your past self. 💬
🔥 If you feel that the blogger’s article is not bad, please 👍 follow, like, and bookmark to support the blogger 👍
Column Series (click to unlock) Learning Route (click to unlock) knowledge orientation 🔥Python Full Stack Whitepaper🔥 Zero-based introductory articles It’s easy to get started in an easy-to-understand way, and you will completely fall in love with the charm of Python. Advanced Grammar It mainly focuses on multi-threaded programming, regular expression learning, and project exercises that are close to actual combat. Office Automation Realize the automatic operation of daily office software, save time and improve office efficiency. Automated testing in practice From the perspective of actual combat, one step ahead of others, rapid transformation of test and development engineers. Database development in practice Master the knowledge of relational and non-relational databases, and improve the ability of actual database development. Introduction and actual combat of reptiles updating Data Analysis updating Front-end entry + flask full stack updating django+vue full stack updating Outreach – Introduction to Artificial Intelligence updating The road to cybersecurity Stepping on the pit Record the pits encountered in the process of learning and drills, so that the later ones can catch up Internet Security Knowledge Literacy Three days of fishing without a deep understanding of the principles will only make you a script kid. vulhub shooting range vulnerability recurrence It makes vulnerability reproduction easy, and allows security researchers to focus more on the vulnerability principle itself. Shell programming articles It does not involve the Linux foundation, and the final case will be biased towards the direction of security reinforcement. [to be completed] WEB Vulnerability Attack and Defense The update will stop on September 3, 2021, and move to safe communities and small secret circles such as the Prophet community Penetration Tool Use Collection The update will stop on September 3, 2021, and move to safe communities and small secret circles such as the Prophet community little bit engineer Test Artifact – Charles Software test data packet capture and analysis artifact Test Artifact – Fiddler Yiwen can learn to fiddle, and it is rare to learn how to eat upside down! Test Artifact – Jmeter It is not only a performance testing artifact, but also can be used to build a lightweight interface automated testing framework. RobotFrameWork An automated testing tool implemented by Python, this chapter only introduces the UI automation part. Java implements UI automation The document was written in 2016, and the UI automation implemented by Java is still useful for reference. MonkeyRunner There are not many application scenarios of this tool at present, the document has been deleted, and it is kept for the sake of good-looking layout.
- 🐳 Why use outer join
- 🐳 Introduction to Outer Joins
The content of this chapter is the outer join of multi-table join query, because MySQL is a [relational database] , and the data is split and reorganized in multiple data tables. Therefore, we are bound to extract data from multiple data tables, and we can achieve multi-table query through inner join and outer join of SQL statements. This part of the content requires us to focus on learning. During the learning process, a variety of cases will be interspersed to strengthen the use of the syntax of table joins.
🐳 Why use outer join
Before explaining why “outer joins” are used, let’s look at one record. (as follows:)
In view of the fact that Zhang San has no department number in the table, we will classify him as a “temporary worker” for the time being, and there is no fixed department establishment.
In such a scenario, the problem arises. When we want to query the name of each employee and his department, in the case of using inner join, because our link condition is
"Department Number" of "Employee Table" = "Department of Department Table" number", "Zhang San" will be missed. Although "Zhang San" does not have a "department number", he is also a member of the company as a "temporary worker", so this problem can only be solved by introducing an outer join syntax, otherwise some logical data will be lost.</p> </blockquote> <h1>🐳 Introduction to Outer Joins</h1> <blockquote> <ul> <li>The difference between outer join and inner join:<ul> <li>Only records that meet the connection conditions will appear in the result of the inner join, and records that do not meet the connection conditions will never appear in the result set.</li> <li>The outer join is very special, whether it meets the data connection conditions or not, it will appear in the result level. (For example, the query of employee department label information mentioned above, because "Zhang San" does not have a department number, if an inner join is used, "Zhang San" does not meet the "connection conditions" and will not appear in the result set. ; replace it with "outer join" and it won't be missed.)</li> </ul> </li> </ul> <p>
e.empno, e.ename, d.dname
LEFT JOIN t_dept d ON e.deptno = d.deptno;
— When connecting, the “employee table” and “department table” are still linked , but the connection keyword is changed from “JOIN” to “LEFT JOIN” (explained in detail below)
— The connection condition of the two tables is still connected using the “ON” keyword, and the connection condition is still “Department Number” of ” Employee Table” = ” Department Number” of “Department Table ”
— LEFT JOIN is “left outer join ” of “outer join” ; (in “outer join” , it is divided into “left outer join” and “right outer join” )
— In this SQL statement, “LEFT JOIN” has data tables “t_emp e” and “t_dept d” on the left and right
— so the “left join” here means: keep all records in the left table, and then go to the right table Connection, if the right table has records that meet the conditions, the normal connection can be done;
— If the right table does not have a qualified connection record, the right table will display the “NULL” value to match the “left table”
</p> </blockquote> <h2>🐬 Left join and right join</h2> <blockquote> <ul> <li>"Left join" is also called "left outer join", which is to keep all the records in the left table and join with the right table. If the right table has records that meet the conditions, it will be connected to the left table; if the right table has no records that meet the conditions, it will use "NULL" to connect to the left table.</li> <li>What is different from "left join" is "right join". "Right join" is opposite to "left join", which is to keep all records of the right table and the qualified records of the left table for connection; similarly, if the left table does not meet the conditions Condition records, use "NULL" to connect with the right table.</li> </ul> <hr /> <p>Example of right join SQL statement:</p> <p>``` SELECT e.empno, e.ename, d.dname FROM t_dept d RIGHT JOIN t_emp e ON e.deptno = d.deptno;</p> <p>-- There is a point to note here, that is, compared with the "left join" above, the positions of the two tables on the left and right of the "right join" here have been swapped ```</p> <p>Here, you can see that the records of "Zhang San" without the "department number" can still be found. So the difference between "left join" and "right join" is not very big.</p> </blockquote> <h2>🐬 Outer join exercise①</h2> <blockquote> <ul> <li>Query the name of each department and the number of people in the department?</li> <li>This question seems to be simple, but there are two difficulties in it, and there are also error-prone places. For details, please refer to the following SQL statement examples and schematic diagrams.</li> </ul> <p><code>SELECT d.deptno, d.dname, COUNT(*) FROM t_dept d LEFT JOIN t_emp e ON d.deptno = e.deptno GROUP BY d.deptno;</code></p> <p>OK, that's where the problem starts.</p> <p>Everyone pay attention to the "40" - "OPERATIONS" department here. There is actually no one in this department, that is, the number of people is "0", but the strange thing is that when the statistics are performed here, the number of statistics "1" appears, Why is this?</p> <p>This is because when we use grouping, we use "left join" to retain all the data in the left table, so we group according to the "deptno" of the left table. (Because the records of the left table are reserved, the grouping also needs to be grouped according to the left table.); The next important part is "COUNT(<em>)", which counts all valid records . Therefore, when all the records in the left table "t_dept" are connected with the right table "t_emp", the right table will use the "NULL" value to connect with the left table "t_dept", and after the connection is completed, it will be a valid record, so since it is valid record, then the result counted by "COUNT(</em>)" is "1".</p> <p>Therefore, it is understandable that the statistical result of 40 departments is "1", but this result is not what we want, how to solve it? Refer to the SQL statement below.</p> <hr /> <p><code>SELECT d.deptno, d.dname, COUNT(e.deptno) FROM t_dept d LEFT JOIN t_emp e ON d.deptno = e.deptno GROUP BY d.deptno;</code></p> <p>This SQL statement is still very good, there are so many details and unconsidered situations, you will only notice these attacks when you actually write it again.</p> </blockquote> <h2>🐬 Outer Join Exercise ②</h2> <blockquote> <hr /> <p><code>(SQL query statement) UNION (SQL query statement) -- If there are multiple query statements, you can continue to use the UNION keyword to connect</code></p> <p>PS: It should be noted here that it does not matter how many result sets are merged by "UNION". The key is that the number of fields and the names of the fields in these result sets must be the same. If the first SQL query statement returns 10 fields and the second returns 2 fields, this situation is completely impossible to combine.</p> <p>``` (SELECT d.deptno, d.dname, COUNT(e.deptno) FROM t_dept d LEFT JOIN t_emp e ON d.deptno = e.deptno GROUP BY d.deptno) UNION (SELECT d.deptno, d.dname, COUNT(*) FROM t_dept d RIGHT JOIN t_emp e ON d.deptno = e.deptno GROUP BY d.deptno);</p> <p>-- The first query statement, the result set obtained is the number of people in each department. -- The second query statement, the result set obtained is the number of people belonging to each department, but because "Zhang San" is a "temporary worker" who does not belong to a department -- so the result sets of the two query statements are combined without Save as shown below.