Connection query knowledge point

Why do we need to learn table connection queries?

 

First, it still corresponds to the previous 2StudentAnd subject

If I want to display the subject name, score, and student name, the subject name and score belong to the subject table.The student name belongs to the student table.We can only use subqueries to solve this problem until we have no learning table connection queries.

However, the performance of related subqueries is not high., If we look like the following query, it is very simple to achieve the same effect

We can find that fromFollowed by 2Tables with aliases for easy reference. Different columns in different tables are called table join queries.

Note that the table alias is optional, but when 2The name of the reference condition column in a table must be 2Take an alias for a table and reference it using an alias. At the same time, you can make a good habit of referencing a column name through the table name or table alias during multi-table join queries, this is equivalent to clearly telling the database engine which table the current column is from, which can slightly improve the performance.

 

 

Table connection mainly involves 3Internal Connection, external connection, and cross-connection. Our main applications and the first twoType

 

 

 

Internal Connection:

 

[Inner] join

InnerOptional

 

In fact, the previous example is an internal connection,It is also called equal connection. Another internal connection method is used for natural connection.NATURAL JOINSyntax.DBMSNot supported and not flexible to use.

 

Left Outer Join:

Left [outer] join

OuterAvailable or not

 

 

 

It seems that the result is no different from the previous inner connection. Now let's add 2Students

 

 

 

 

That is, the 2Students did not take any tests. Now we use studentSubject joined to the left outer tableExecute the same query for the table

 

We found that students who did not take the test are also identified, that is, studentAll the data in the table is queried, whether in subject or not.For the student ID. No matter whether you take the test or notThe table exists and is displayed.

 

Now let's switch the connection location

 

Now the result is normal.

We can conclude that the left Outer Join is to query all the values of the columns in the left table in the query column. The values of the columns in the left table depend on 2.Join condition for a tableLeft table,Right table)

For example, the above query column lists subjects, scores, and names.

If student is usedTable to left join subjectTable, studentThe table is the left table, subjectThe table is the right table.

The name to be queried belongs to the left table student.Therefore, regardless of the student ID in the subjectThere is no corresponding value in the table. No matter whether the student has taken the test or not, the name of all the students is displayed in the final result. The subject and score belong to subject.In the right table, the data displayed depends on the data according to stu. s_id = sub. stu_idThe connection query condition. That is, the student ID corresponding to the subject and score is in studentWhether the left table exists or not. Otherwise, a null value is displayed.

The same is true for the corresponding right connection, except for changing the direction.

 

Outer right connection

Right [outer] join

For previous studentTable left Outer Join subjectTable, we use the right Outer Join. To achieve the same effect, we only need to change the direction.

StudentLeft Outer Join subject Change to subject.Right Outer Join student

 

 

 

 

Full outer join: full [outer] join,Regardless of the connection conditions, directly set 2All data in the table is queried. If no matching value is found for the query results of a table in the other table based on the connection conditions, a null value is returned.