Table 1:student
Table 2:courseyuwen Language Score
One, outer connection
External connection points: Left join, right connection, full outer connection.
1. Left-side connection to the Ieft join or to the outer join
SQL statement: SELECT * from Student LEFT join Courseyuwen on student.num = Courseyuwen.num
Results:
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 Courseyuwen on student.num = Courseyuwen.num
Results:
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 Courseyuwen on student.num = Courseyuwen.num
Two, inner join join or INNER JOIN
SQL statement: SELECT * from Student inner join Courseyuwen on Student.num=courseyuwen.num
Results:
Inner JOIN is a comparison operator that returns only rows that meet the criteria.
This is equivalent to: SELECT * from Student,courseyuwen where student.num=courseyuwen.num
Iii. Cross Join
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 Courseyuwen
If we add a WHERE clause to this SQL at this time, such as SQL:
SELECT * FROM student cross join Courseyuwen where student.num=courseyuwen.num
Four, two table relationships are one-to-many, many-to-many or multiple-long connection statements
Table 3:course Credits
Select students with scores in the language and display the scores and credits for the language occupation
SQL statement: Select S.num,s.name,cy.score,c.credit from Student as S left join Courseyuwen as CY on S.num=cy.num left join Course As C on c.cid=1002
Results:
Multi-Table Query