Multi-Table connection query and table connection

Multi-Table connection query and table connection

 

I. Overview 1 Background

Theoretically, it is difficult to put all the data into the same table. In fact, even if it is implemented, the table is huge and redundant, which is not easy to query and maintain, therefore, when you store different data in different tables, you need to connect them for query.

2. Execution Process

When two tables are connected for query, compare each row of data in one of the tables with all the data in the other table. If the given conditions are met, merge the data of the two rows into one row. Otherwise, the data is discarded and the remaining rows are compared.

3 Descartes

If no join condition is set during table connection, the number of returned query results is the product of the number of rows in two tables. This phenomenon is called Descartes.

Two-table join query methods 1. Inner join

The two tables are connected for query. If only matching data is displayed, internal join is used.
Format:

Select t1.columnName01, t2.columnName02 from tbName01 t1 (inner) join tbName02 t2 on connection conditions;

To specify the table to which the field belongs, specify an alias for each table that participates in the connection and call the field in the alias.

2. External Connection

The outer join returns all the data in the table to which the connection points. If the field of the other table does not match the data, it is filled with null.
Format:

Select t1.columnName01, t2.columnName02 from tbName01 t1 right/left (outer) join tbName02 t2 on connection condition;

If left is used in Outer Join, all data in the left table is taken out; If right is used, all data in the right table is taken out.

3 n> = join queries for three tables

A table A is connected to multiple tables for query, and table A is connected to other tables respectively. The final result is A combination of the same A table field data in the query results of each independent connection, all fields in Table A of A row in A join query result are retrieved. If all other join query results have this field, all rows with this field are merged into one row.

The following is an example of multi-to-Multi-Association.

Student table tb_student:

Course schedule tb_course:

The intermediate table tb_mid (using the joint primary key, each primary key field is a foreign key field, pointing to the corresponding primary key of the student table or curriculum respectively ):

Task: Query all courses selected by student Li Si.

Analysis: there is no direct relationship between the student table and the curriculum, but the student table has a relationship with the intermediate table and the curriculum and the intermediate table. Therefore, the intermediate table is used as the main table for connection query with the student table and curriculum respectively.

Connect the intermediate table to the student table and record the query results as Table:

select s.id,s.name,m.course_id,m.student_id from tb_student s join tb_mid m on s.id=m.student_id;

 

Link the intermediate table with the course schedule and record the query results as Table B:

select c.id,c.name,m.course_id,m.student_id from tb_course c join tb_mid m on c.id =m.course_id;

Retrieve all the data of the master table (tb_mid) from A row in Table A, and compare it with all the rows in Table B. If the corresponding field value of the master table is the same, merge it into one row, in this loop, the query results of the three tables are formed:

The above is the execution process of the join query for the following three tables:

select s.id sid,s.name sname,c.id cid ,c.name cname,m.student_id,m.course_id      from        tb_mid m join tb_student s on m.student_id =s.id                 join tb_course c on m.course_id=c.id;

Add a filter condition "where s. name = 'Li si'" to the query results of the three tables to obtain the final result.