Related queries in MySQL (internal connection, outer connection, self-connection)

Related queries in MySQL (internal connection, outer connection, self-connection)

When using the database query statement, the single table query sometimes can not meet the project business requirements, in the project development process, there are many needs are involved in multi-table connection query, summarize the MySQL Multi-table Association query

One, internal connection query

The result of all queries is the ability to have corresponding records in the connected table.

Take T_employee (Employee table) and T_dept (department table) as an example:

The records in the T_employee table are as follows: Dept represents the department where the employee is located

The following is recorded in the T_dept table:

It can be found that there are no employees in the Human resources department (this is only an example, may not be true, but mainly in the logical relationship), and Zhao Qi no corresponding department, now want to find out the name of the employee and its corresponding department name:

At this point, you will use the INNER join query, keyword (inner join)

Here is the idea of associating query SQL, 1, first determine the connected table, 2, and then determine the field to query, 3, determine the connection conditions and connection mode

1. Select
2. E.empname,d.deptname
3. From t_employee e
4. INNER JOIN t_dept d
5. On e.dept = d.id;

The results of the query are as follows:

Among them, no Department of personnel and departments are not employees of the department is not queried, this is the characteristics of the internal connection, only query in the connected table can have corresponding records, where e.dept = D.id is the connection condition

Second, left outer connection query

Refers to the data of the table on the left, to match the data on the right table, if matched to the display, the match is not displayed as null. For example:

Check all employee names and his department name: in the connection of Zhao Seven did not be detected, because he did not have the corresponding department, now want to put Zhao Seven also find out, you need to use the left outer connection:

1. SELECT E.empname,d.deptname
2. From t_employee e
3. left OUTER JOIN t_dept d
4. On d.id = e.dept;

Here, T_employee is the left table, which is the Datum table, using Datum table data to match the data of the right table, so the records of the left table are all queried, if the right table does not record the corresponding words will show null

Query Result:

The keyword is the left outer join, which is equivalent to the Ieft join, in the correlation query, does the outer join query is the right connection query, both is a concept

Third, the right outer connection is the same, but the position of the benchmark table has changed.

For example: query all departments and the corresponding employees:

1. SELECT E.empname,d.deptname
2. From t_employee e
3. right OUTER JOIN t_dept d
4. On d.id = e.dept;

Here just to modify the left to right, but the datum table changes, is to match the data in the table to the back of the table, so the left outer connection can do the query, right outside the connection can also do
Query Result:

Four, full outer connection

As the name implies, the two table fields are detected, there is no corresponding value to display NULL, but note: MySQL is not fully connected (MySQL does not have the full outer join keyword), want to achieve the effect of the full outer join, You can use the Union keyword to connect left and right outer joins. For example:

1. Select E.empname,d.deptname
2. From t_employee e
3. left JOIN t_dept d
4. On e.dept = D.id
5. UNION
6. Select E.empname,d.deptname
7. From t_employee e
8. right JOIN t_dept D
9. On e.dept = d.id;

Query Result:

If you're in Oracle, connect two tables directly using the full outer join keyword.

Five, self-connected query

The self-connected query is the connection query between the current table and itself, the key point is to virtualize a table to give an alias

For example: Query the name of the employee and his boss, because the boss is also an employee, so here to virtualize a boss table

1. SELECT E.empname,b.empname
2. From t_employee e
3. left JOIN t_employee b
4. On e.bossid = b.id;

Query Result:

Here, Table B is a virtualized table, and we can understand the records of table B by querying:

1. SELECT e.empname,b.empname,b.*
2. From t_employee e
3. left JOIN t_employee b
4. On e.bossid = b.id;

Query Result:

The next four fields are all the records of the virtualized B-table, but looking at these four fields is actually a record of all the employees who are bosses.

Therefore, a self-join query is generally used as the value of a field in a table that refers to a value in another field, such as a permission table, and the parent permission is also a permission.

Related queries in MySQL (internal connection, outer connection, self-connection)