MySQL connection Query

MySQL connection query is expected to be used in database query operations. The following describes how to use MySQL connection query for your reference.

First, we will make two tables: employee information table and department information table. Here, the table is created to show the concept of connection, so the field is very simple.
EmployeeTB employee information table ):

Employeeid employeename deptid
0001 Zhang san01
0002 Li Si 01
0003 Wang Wu 02
0004 Zhao liu02
0005 Zheng Qi NULL

DeptTB Department info table)
Deptid deptname
01 Technology Department
02 Marketing Department
03 Engineering Department

Now we need to perform a connection query to connect two tables to retrieve data. The employee ID, employee name, and department name in the employee information table are retrieved respectively.
Obviously, the join condition for the two tables is the department ID of the employee table = The Department ID of the Department table.
Note: Zheng Qi does not belong to any new employee in any department and has not been assigned to any department), while the Engineering Department does not have any employees, such as a newly established Department and has no employees)

MySQL connection query:

1. Internal Connection Query
We can use two methods, which are equivalent.
One is: SELECT e. employeeid, e. employeename, d. deptname FROM EmployeeTB AS e, DeptTB AS d WHERE e. deptid = d. deptid
The other one is: SELECT e. employeeid, e. employeename, d. deptname FROM EmployeeTB AS e inner join DeptTB AS d ON e. deptid = d. deptid
The search results are as follows:

Employeeid employeename deptname
0001 Zhang San Technology Department
0002 Li Si Technology Department
0003 Wang Wu Marketing Department
0004 Zhao 6 Marketing Department

However, the information of "Zheng Qi" and "Engineering Department" will not be retrieved. When using inner join calculation, you must ensure that the connection condition e. deptid = d. deptid matches and the result is retrieved. When we connect two pieces of data for retrieval, we first scan the records in the "employee info table" row by row, and then determine whether the record is retrieved Based on the connection conditions. For example, for Michael Zhang, the deptid of this record is the 01 Department number.) In the department table, it can find the Department ID 01 that matches it, and the department name of the 01 is deptname) the record is "Technical Department", so Michael Zhang will be retrieved. The final result must be:

0001 Zhang San Technology Department

Similarly, Li Si, Wang Wu, and Zhao Luang can. However, Zheng Qi's Department number is NULL, and no matching item can be found in the department information table because the Department information table does not have a department with the department number being NULL. Therefore, Zheng Qi will not be retrieved.
Similarly, no team ID is 03, so the records of the Engineering Department will not be retrieved.

2. left Outer Join
But in some cases, we need to know the information of all employees, even if they do not belong to any department. In this way, we can use an external join. Here, the left Outer Join is used, that is, the records in the left table of the join. no matter whether the matching items can be found in the right table, we need to search, if no matching item exists, the field value in the right table is NULL.) In this example, this employee does not belong to any department.
The search statement is:
SELECT e. employeeid, e. employeename, d. deptname FROM EmployeeTB AS e left outer join DeptTB AS d ON e. deptid = d. deptid
The search results are as follows:

Employeeid employeename deptname
0001 Zhang San Technology Department
0002 Li Si Technology Department
0003 Wang Wu Marketing Department
0004 Zhao 6 Marketing Department
0005 Zheng Qi NULL

However, the engineering department will not be retrieved here, because deptname is in the table on the right of the connection, and the engineering department does not have any records in the left table, so it will not be retrieved. Here we are concerned with the "table on the left of the Connection"

3. Right Outer Join
Sometimes, we need to know the information of all departments, even if it does not have any employees. In our query, the Department table is on the right of the connection. If we want to know all the records in the right table, we can use the right outer join, if no matching item is found in the table on the left side of the record, the corresponding field "employee ID" and "employeename" are NULL.
The search statement is:
SELECT e. employeeid, e. employeename, d. deptname FROM EmployeeTB AS e right outer join DeptTB AS d ON e. deptid = d. deptid
The search results are as follows:

Employeeid employeename deptname
0001 Zhang San Technology Department
0002 Li Si Technology Department
0003 Wang Wu Marketing Department
0004 Zhao 6 Marketing Department
NULL Engineering Department

However, Zheng Qi won't be retrieved here because he cannot find the matching item in the right table. Here, he focuses on the "table on the right of the Connection"

4. Full outer connection
What if we want to know all the records? Whether employees have departments or departments, we need to search for them. You can use a full outer connection. Pay attention to the two parts in the connection. If there is no department, the Department is empty, there is no employee, and the employee information is empty.
The search statement is:
SELECT e. employeeid, e. employeename, d. deptname FROM EmployeeTB AS e full outer join DeptTB AS d ON e. deptid = d. deptid
The search results are as follows:

Employeeid employeename deptname
0001 Zhang San Technology Department
0002 Li Si Technology Department
0003 Wang Wu Marketing Department
0004 Zhao 6 Marketing Department
0005 Zheng Qi NULL
NULL Engineering Department
 
 

Mysql multi-Table query implementation

Mysql temporary table usage

MySQL Stored Procedure for table splitting

In-depth discussion on MySQL Lock Mechanism

Detailed description of MySQL Data Table types