SQL multi-table connection Query Instance analysis (detailed graphics and text) _mssql

Create a new two sheet:

Table 1:student screenshot as follows:



Table 2:course screenshot as follows:



(This is just a table to demonstrate the connection of SQL statements, of course, in the actual development we do not build tables, the actual development of the two tables will have their own different primary key.) ）

One, outer connection

Outer joins can be divided into: Left join, right connection, complete outer connection.

1, left join a left join or left OUTER join

SQL statement: SELECT * FROM student left join course on Student.id=course.id
Execution results:



The left OUTER join contains all the rows in the left table of the left-hand join, and if a row in the left table does not match the right table, the portion of the corresponding row in the result is empty (null).

Note: At this point we cannot say that the number of rows in the result equals the number of rows in the left table data. Of course, the number of rows in the query result is equal to the number of rows in the left table data, because the left and right tables are one-to-one relationships at this time.

2. Right-join or right-hand outer join
SQL statement: SELECT * FROM student right join course on student.id=course.id
Execution results:



The right outer join contains all of the rows in the right table of right-click, and if a row in the left table does not match in the right table, the portion of the corresponding left table in the result is empty (null).

Note: Also at this point we cannot say that the number of rows in the result equals the number of rows in the right table. Of course, the number of rows in the query result is equal to the number of rows in the left table data, because the left and right tables are one-to-one relationships at this time.

3. Fully outer join or full outer join

SQL statement: SELECT * FROM student full join course on student.id=course.id
Execution results:



A full outer join contains all the rows in the left and right two tables if a row in the right table does not match in the left table, the portion of the right table for 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, then the left table portion of the corresponding row in the result is empty (null).

second, the inner join join or INNER JOIN

SQL statement: SELECT * FROM student inner JOIN course on student.id=course.id
Execution results:



The inner join is a comparison operator that returns only the rows that meet the criteria.

This is equivalent to: SELECT * from Student,course where student.id=course.id

cross-connect 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 results:



If we add a WHERE clause to this SQL at this time, like Sql:select * from Student Cross join course where student.id=course.id

The result set that matches the criteria is returned, and the result is the same as the result shown in the inner join.

four, two-table relationship is One-to-many, one-to-many or Many-to-many connection statement

Of course the above two tables are one-to-one, so if Table A and table B are one-to-many, One-to-many, or many-to-many, how do we write connected SQL statements?

In fact, two more than a pair 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 slightly changed.

For example, the columns in table 1 can be read as follows:
Sno Name Cno
The columns in table 2 can be read as follows:
Cno CName
So the two tables can write a one-to-many and many-to-many SQL statement, just like the one on the above one-to-one SQL statement.

Here's how to build a table and some SQL statements when two tables are many-to-many.

New Three tables:

Table A:student screenshot as follows:



Table B:course screenshot as follows:



Table C:student_course screenshot as follows:



A student can choose multiple courses and a course can be chosen by multiple students, so there is a many-to-many relationship between the student table student and the curriculum course.

When two tables are many-to-many, we need to create an intermediate table Student_course, with at least two primary keys for the middle table and, of course, other content.

SQL statement: Select S.name,c.cname from Student_course as SC left join student as s on S.SNO=SC. Sno left JOIN course as C on C.CNO=SC. Cno

Execution results:



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