03--sqltie Few words sqltie link (join)

This article will start with the theory and syntax of the connection, and in combination with specific examples, analyze SQL connections in detail.

Prior to the database connection operation indefinitely, probably know what things, but the interview when the test was abused into a slag, not clear what is the connection. A fall into, your wit. This is the so-called indefinitely, just simple reading is useless, only hands-on experiments to thoroughly understand what is connected.

Connection types and Conditions

Each connection operation in SQL includes a connection type and a connection condition.

Connection type

The connection type determines how records that do not match the join condition are handled.

Connection Type	return Results
INNER JOIN	Contains only records in the left and right tables that meet the join criteria
Left OUTER JOIN	On the basis of an inner join, add a record in the left table that does not match the right table, and the remaining field is assigned a value of NULL
Right outer join	On the basis of the inner join, add a record in the right table that does not match the left table, and the remaining field is assigned a value of NULL
Full OUTER JOIN	A combination of left and right outer joins.
Cross Join	Equivalent to an inner join without a join condition (that is, to produce a Cartesian product)

The keywords inner and outer are optional because, depending on the rest of the connection type, we can tell that the connection is internal and outer. To put it simply: In addition join to a separate internal connection, the others are outer joins.

Connection conditions are necessary for external connections, but for internal connections, the connection conditions are optional (if omitted, a Cartesian product will be produced).

Connection conditions

The join condition determines which records in two tables match each other and which properties appear in the connection results.

Connection Conditions	decorated position	Semantics
Natural	Before connection type	Concatenate all common fields between two tables for equal records, merging the same columns
On < predicates >	After connection type	Join a record that conforms to a predicate and does not merge the same columns
Using (A1, A2,..., an)	After connection type	A subset of natural semantics, connected to only two tables (A1,A2,.. An) to merge the same columns in the public field

As you can see from the above description: The connection operation is a combination of the connection type and the connection condition, only in this premise can really understand the function of the connection.

FQA

The table used in the example

Student+----+--------+| ID | Name |+----+--------+|1| Zhang San | |2| John Doe | |3| Wang ER | |4| The Junior | |5| Second day |+----+--------+Teacher+----+-----------+| ID | Name |+----+-----------+|1| Teacher Wang | |2| Teacher Li | |3| Teacher Zhang | |4| Teacher Xiao | |5| NULL | |6| Teacher Chen |+----+-----------+Course+----+--------+------+| ID | CNAME | Tid |+----+--------+------+|1| Math |1||2| English |2||3| language |3||4| Sports |1||5| Physics | NULL |+----+--------+------+Student_course+-----+-----+| Sid | CID |+-----+-----+|1|1||1|2||1|3||2|1||2|4||3|5||3|6||4|4|+-----+-----+

What is the number of result sets after the inner join? Equal to the number of records in the left or right table?

teacher course Results of internal connections

 select  * from  teacher INNER JOIN Course on teacher.id = Course.tid;  +----+-----------+----+--------+------+| ID | name | ID | CNAME |  Tid |+----+-----------+----+--------+------+| 1  |  Miss Wang | 1  |    Math |  1  | | 2  |  Teacher Li | 2  |    English |  2  | | 3  |  Miss Zhang | 3  |    language |  3  | | 1  |  Miss Wang | 4  |    Sports | 1  |+----+-----------+----+--------+------+ 

It can be found that teacher Wang at the same time teach maths and sports, so the left table in the teacher Wang match two records in the right table, physics without teacher teaching, so did not appear in the results. Description The number of result sets within a connection is equal to the number of matching records in the left and right tables .

What is the number of result sets after the left connection? equals the number of records on the left table?

teacher course results of Left JOIN connection

Select* fromTeacher left JOIN course on teacher.id =Course.tid;+----+-----------+------+--------+------+| ID | name | ID | CNAME | Tid |+----+-----------+------+--------+------+|1| Miss Wang |1| Math |1||2| Teacher Li |2| English |2||3| Miss Zhang |3| language |3||1| Miss Wang |4| Sports |1||4| Miss Xiao | NULL | NULL | NULL | |5| NULL | NULL | NULL | NULL | |6| Teacher Chen | NULL | NULL | NULL |+----+-----------+------+--------+------+

As you can see, teachers without a teaching course also appear in the results, and the corresponding fields are null. Indicates that the number of result sets is not equal to the number of left table records because two tables are directly not a one-to-many relationship. The number should be equal to the number of result sets within the connection plus the number of records that do not match in the left table .

Not supported in Mysql full outer join

Can be union simulated by operation.

SELECT *From teacherleft JOIN course on Teacher.id=Course.tidunionselect*From teacherright JOIN course on Teacher.id=course.id;+------+-----------+------+--------+------+| ID | name | ID | CNAME | Tid |+------+-----------+------+--------+------+|1| Miss Wang |1| Math |1||2| Teacher Li |2| English |2||3| Miss Zhang |3| language |3||1| Miss Wang |4| Sports |1||4| Miss Xiao | NULL | NULL | NULL | |5| NULL | NULL | NULL | NULL | |6| Teacher Chen | NULL | NULL | NULL | |4| Miss Xiao |4| Sports |1||5| NULL |5| Physics | NULL |+------+-----------+------+--------+------+Multi-table connection issues Consider a record of all students ' coursesSelect* fromstudentleft join Student_course on Student.id=student_course.sidleft Join course on Student_course.cid=course.id;+----+--------+------+------+------+--------+------+| ID | name | Sid | CID | ID | CNAME | Tid |+----+--------+------+------+------+--------+------+|1| Zhang San |1|1|1| Math |1||2| John Doe |2|1|1| Math |1||1| Zhang San |1|2|2| English |2||1| Zhang San |1|3|3| language |3||2| John Doe |2|4|4| Sports |1||4| The Junior |4|4|4| Sports |1||3| Wang ER |3|5|5| Physics | NULL | |3| Wang ER |3|6| NULL | NULL | NULL | |5| Second day | NULL | NULL | NULL | NULL | NULL |+----+--------+------+------+------+--------+------+

Use the student table to connect the intermediate table, and then connect the curriculum to get results. The join operation is for two tables, so the above results are from left to right and 22 connected.

If you have more questions about the connection, or find errors in the article, please contact us

Resources

"Database System Concept"

03--sqltie Few words sqltie link (join)