SQL multi-table connection query (detailed instance)

Transferred from: http://www.dedecms.com/knowledge/data-base/sql-server/2012/0709/2872.html

This article mainly lists two and three tables to tell the multi-table connection query.
New Two sheets:
Table 1:student is as follows:

Table 2:course is as follows:

(In this case, the table is just to demonstrate the connection SQL statement, of course, we do not create a table in the actual development, the actual development of the two tables will have their own different primary keys.) ）
One, outer connection
The outer connection can be divided into: Left join, right connection, complete outer connection.
1. Left join the left JOIN or left OUTER join
SQL statement: SELECT * FROM student left join course on Student.id=course.id
Execution Result:

The left OUTER join contains all the rows from the left table in a left join, and if there is no match on the right table in the table, the portion of the right table in the corresponding row in the result is all empty (null).
Note: We cannot say at this point that the resulting row count equals the number of rows in the left table data. Of course, the number of rows in this query result is equal to the number of rows in the left table data, because the left and right tables are one-to-one.
2. Connect right JOIN or starboard outer JOIN
SQL statement: SELECT * FROM student right join course on student.id=course.id
Execution Result:

The right outer join contains all the rows in the right table, and if a row in the left table does not match on the right table, the portion of the corresponding left table in the result is all empty (null).
Note: Also at this point we cannot say that the resulting row count equals the number of rows in the right table. Of course, the number of rows in this query result is equal to the number of rows in the left table data, because the left and right tables are one-to-one.
3. Fully external connection full join or outer join
SQL statement: SELECT * FROM student full join course on student.id=course.id
Execution Result:

A full outer join contains all of the rows in both the left and right tables of the complete join, and if there is no match in the left table for a row in the table, the portion of the right table in the corresponding row in the result is all empty (null), and if a row in the left table does not match in the right table, the portion of the left table
Two, inner join join or INNER JOIN
SQL statement: SELECT * FROM student inner JOIN course on student.id=course.id
Execution Result:

Inner JOIN is a comparison operator that returns only rows that meet the criteria.
This is equivalent to: SELECT * from Student,course where student.id=course.id
Iii. Cross Join
1. Concept: A cross join without a WHERE clause will produce a Cartesian product of the table involved in the connection. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set.
SQL statement: SELECT * FROM student Cross Join course
Execution Result:

If we add a WHERE clause to this SQL at this time such as Sql:select * from Student Cross join course where student.id=course.id
The result set that matches the condition is returned, and the result is the same as the inner join shows.
Four, two table relationships are one-to-many, many-to-many or multiple-long connection statements
Of course, the above two tables are a one-to-one relationship, so how do we write a concatenated SQL statement if Table A and table B are a pair of many, many pairs, or many to many?
In fact, two tables of SQL statements and one-to-one SQL statements are similar, but the results of the query is not the same, of course, the two tables are also slightly modified.
For example, the columns in table 1 can be changed to:
Sno Name Cno
The columns in table 2 can be changed to:
Cno CName
In this way, two tables can write one-to-many and many-to-several SQL statements, just as you would with a single SQL statement above.
Here's a look at how we build tables and SQL statements when the two tables are many-to-many.
Create three new tables:
Table A:student is as follows:

Table B:course is as follows:

Table C:student_course is as follows:

A student can choose multiple courses, a course can be selected by multiple students, so the student table student and curriculum course is a many-to-many relationship.
When the two tables are many-to-many relationships, we need to create an intermediate table Student_course, the intermediate table must have at least two tables of the primary key, of course, there can be other content.
SQL statements: Select S.name,c.cname from Student_course as SC left joins student as s on S.SNO=SC. Sno left joins course as C on C.CNO=SC. Cno
Execution Result:

The result of this SQL execution is the case of the student's course selection.

SQL multi-table connection query (detailed instance)