MYSQL Multi-Table query

Multiple table queries are bound to use multiple table associations

Multiple table associations are divided into three categories:

A: Internally joined join or inner join

B: External links outer joins are divided into two categories: Left outer join and right outer join.

C: Cross Join

The following examples show that there are two tables

One of the students ' teacher's a middle table

First, the internal link application scenario

This condition is obtained by a B common data while meeting the conditions of AB called internal connection

The code is:

SELECT *from Stu_tea St JOIN student s on St. Student =s.name

The result is:

It is clear from the table that the information in the Studnet table is stitched up behind the association in accordance with the rules (the Name field in the Student Fields =student table in the intermediate table), and you can see that there is less information about the students 陈建 and Li Tao, Because the two students of 陈建 and Li Tao do not exist in the same two tables.

If you want to repeat only one row for students and name in the results table, you can do the following with the code

SELECT St. ' Students ', St. ' teachers ', S. ' Birth ' from Stu_tea St JOIN student s on St student =s.name

The results are as follows:

The S.birth can also be processed in Chinese, followed by an alias display:

SELECT St. ' Students ', St. ' teacher ', s. ' Birth ' birthday ' from Stu_tea St JOIN student s on St. Student =s.name

The results are as follows:

Note: The above code in the Text field, plus no quotation marks can be, the system generally by default plus single quotation marks;

Second, external link application scenario

Left outer connection

This scenario is to get all the data of A and B to meet certain conditions of the partial data

The code is: SELECT *from Stu_tea St left JOIN student s on St. ' student ' =s. ' Name '

The result is:

As can be seen from the table, the left outer connection is completely in the left side of the middle table as a template, the student table in the middle of the back of the label, no data with NULL, the student table and the intermediate table does not show the data

For the above results, you can also use the WHERE clause to further filter

For example, to remove data without student information: SELECT *from Stu_tea St left JOIN student s on St. ' Student ' =s. ' name ' WHERE S. ' Name ' is not NULL

The result is:

We also queried what student information was removed: SELECT *from Stu_tea St left JOIN student s on St. ' Student ' =s. ' name ' WHERE S. ' Name ' is NULL

The result is:

Note: MySQL can only be used with IS cannot use "="

Right outer connection

This scenario is to get all the data of B and a partial data that satisfies certain conditions

The right external connection is completely opposite to the left outer connection, not specifically

Three, cross-linking

In practical applications there is also a situation, to want a A, a record of the permutation combination, that is, Cartesian product, this is not good to use sets and elements to represent. Need to use Cross join

Code is: SELECT *from Stu_tea St Cross JOIN Student

The result is:

Can be found in student table in a total of 5 rows of data, Stu_tea table has 12 rows of data, 60=12*5;

This code can also be written as select *from Stu_tea St JOIN Student

Precautions:

1. The connection table uses a comma, which is interpreted as a cross-connection;

That is, select *from stu_tea st,student Select *from Stu_tea st Join student Select *from Stu_tea St Cross JOIN Student is equivalent to

2. The general internal connection is required to add the on qualification, if not added will be interpreted as a cross-connection

3. A general cross-join (that is, the Intersect join join can) is followed by a where condition, or it can be written on

MYSQL Multi-Table query