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

This article mainly introduces "the basic syntax of multi-table join in MySQL and the way of cross-join". In daily operation, I believe that many people have doubts about the basic syntax of multi-table join and the way of cross-join in MySQL. Xiaobian consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "basic syntax and cross-join way of multi-table join in MySQL". Next, please follow the editor to study!

Catalogue

Basic syntax of multi-table join

Cross connection and Cartesian product phenomenon

Cross connection

Cartesian product phenomenon

Internal connection

External connection

Left outer connection

Right outer connection

Full external connection

Subquery

Basic syntax of multi-table join

Multi-table join, that is, several tables are spliced into one table and then queried

Select field 1, field 2,... from table 1 {inner | lift | right} join table 2on connection conditions

There are two tables: the department table and the employee table.

Cross-connection and Cartesian product cross-connection

Cross connect, also known as unconditional inner connection / Cartesian connection

Each item of the first table will be combined with each item of the other table in turn.

Select * from employee,department

The above results are certainly not what we want to know. Everyone in the left table has four departments. Careful observation of these four records is exactly the result of matching each record in the left table with that in the right table.

Cartesian product phenomenon

The cause of the Cartesian product phenomenon: there is no valid connection condition between the two tables. Since you have no join conditions, the first row in this table must match all the rows in the other appearance, similarly, the second row in this table must be able to match all the rows in the other appearance, and so on, the last row m in this table can also match all the rows in the other appearance. If the other table has n rows, then the last number of rows displayed must be masked n rows.

If you do not want to produce Cartesian product, you need to add valid table join conditions. Taking the above example, the left table dep_id represents their department only if it is equal to the right table id.

Internal connection

INNER JOIN is to find the intersection of several tables, that is, to filter out the correct results according to the criteria.

Select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.namefrom employee as emp INNER JOIN department as depon emp.dep_id=dep.id

This record in the employee table dep_id=5 is not returned because there is no department for id=5 in the department table, and this record is not returned because the administration department has no employees.

External connection left outer connection

The left join (left join) is based on the left table, and if there is no suitable record in the right table, it is completed with NULL; its essence is to add records with results in the left table but not in the right table on the basis of the inner join (the record of this situation will be ignored in the case of inner join).

Select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.namefrom employee as emp left join department as depon emp.dep_id=dep.id

Right outer connection

Contrary to the left join, the right join (right join) is based on the right table, and if some fields in the left table do not have appropriate results, complete with NULL; its essence is to add records with results in the right table but not in the left table on the basis of the inner join (the record of this situation will be ignored in the case of inner join).

Select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.namefrom employee as emp right join department as depon emp.dep_id=dep.id

Full external connection

All the records in the left and right tables are shown on the basis of all external connections and inner connections, and the default records in the left and right tables are completed by NULL.

There is no fully concatenated FULL JOIN syntax in MySQL, but is implemented with UNION/UNION ALL statements.

The difference between UNION and UNION ALL, UNION has the function of deduplication.

Select emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.namefrom employee as emp left join department as depon emp.dep_id=dep.idunionselect emp.id,emp.name,emp.age,emp.dep_id,emp.gender,dep.id,dep.namefrom employee as emp right join department as depon emp.dep_id=dep.id

Subquery

A subquery is a way of nesting one query statement into another query statement:

The inner query result of a subquery can be used as an outer query statement to provide query conditions.

Subqueries can contain keywords such as IN, NOT IN, AND, ALL, EXISTS, NOT EXISTS, and so on.

The subquery can also include comparison operators, such as =,! =, >, 20);-- query the names of employees in the Finance Department select name from employeewhere dep_id in (select id from department where name=' Finance Department');-- query the ages and names of all employees who are older than the average age select name,age from employeewhere age > (select avg (age) from employee)

At this point, the study of "the basic syntax of multi-table join and the way of cross-join in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.