Jion operations in T-SQL

--Creating a Student Table CREATE table Students (Sno nvarchar () NOT null primary key, name nvarchar (+) NOT NULL, Gender nchar (1) Check (gender = ' male ' or gender= ' female ') default (' Male ')) Goinsert into Students (sno,name,gender) VALUES (' S 001 ', ' Zhang San ', ' Male ') insert into Students (Sno,name,gender) VALUES (' S002 ', ' John Doe ', ' Male ') insert into Students (Sno,name,gender)    VALUES (' S003 ', ' Harry ', ' female ') insert into Students (Sno,name,gender) VALUES (' S004 ', ' Zhao Liu ', ' female ') go--create a curriculum created table Course ( CNO nvarchar (ten) not null primary key, name nvarchar (+) not NULL,) Goinsert into Course (cno,name) values (' C001 ', ' math ') insert into Course (cno,name) VALUES (' C002 ', ' language ') insert into Course (cno,name) VALUES (' C003 ', ' English ') insert Into Course (cno,name) VALUES (' C004 ', ' physical ') go--create results for table score (Sno nvarchar () not NULL, CNO nvarchar (() not NULL, score int) Goinsert into score values (' S001 ', ' C001 ', "n"); INSERT into score values (' S002 ', ' C001 ', '); I Nsert into score values (' S002 ', ' C002 ', 94); insert into score values (' S002 ', ' C003 ',--), insert into score values (' S003 ', ' C001 ', "); INSERT into score values (' S003 ', ' C00 2 ', (); INSERT into score values (' S003 ', ' C003 ', 98);--in order to test the full join, intentionally inserting a data that does not exist insert into score values (' XXXX ', ' C003 ', 98);/********************************inner join**************************/--Query Student's number, name, corresponding course name, and score Select St.sno,st.name,co.name,sc.score from Students ST inner JOIN score SC in St.sno = Sc.sno inner join Course CO on sc.cno = Co.cnosno name Name score------------------------------------- --------------------------------------------S001 Zhang San math 91S                             002 John Doe Mathematics 93s002 John Doe Chinese                             94s002 John Doe English 95s003 Harry                 Mathematical            96s003 Harry Chinese 97s003 Harry English 98 (7 lines affected)/********************************left join**************************/-- Consult all students for course information Select St.name,sc.cno from Students stleft join score SC on st.sno=sc.sno/********************************                           Left Join**************************/select St.name,sc.cno from score Scright joins Students ST on St.sno=sc.snoname                             CNO------------------------------------------------------------Zhang San C001 John Doe                             C001 John Doe C002 John Doe C003 Harry  C001 Harry C002 Harry C003 Zhao Liu NULL (8 Rows affected)/********************************full join**************************/--queries all students as well as the score information select * FROM Students Stfull join score SC on ST.SNO=SC.Sno--outer can be omitted, equivalent to the following query select * FROM Students ST full outer join score SC on St.sno=sc.snosno name  Gender Sno CNO Score--------------------------------------------------------                           -----------------------------------------S001 Zhang San male S001 C001                             91s002 Li four men S002 C001 93s002 John Doe      Male S002 C002 94s002 li four men                           S002 C003 95s003 Wang Buiju S003 C001       96s003 Wang Buiju S003 C002 97s003                             Wang Buiju S003 C003 98s004 Zhao Liu Female NULL NULL nullnull null null XXXX C003 98 (9 rows affected)/********************************cross join**************************/--query for "Missing" Student information (with course, no score information)-- Using cross join, generate the Cartesian product of the student and the course, which is all the course information for all the students-and then make a left link to the score table, if the score does not exist, then the "missing" SELECT * FROM (select St.sno, st.name as Sname,                    Co.cno, Co.name as CName from Students ST Cross joins Course CO) T left joins score SC on t.cno = Sc.cno                          and T.sno = Sc.sno where Sc.cno is Nullsno Sname cno CName Sno CNO score-----------------------------------------------------                             ------------------------------------------------------------------------------S001 Zhang San                             C002 language null null NULLS001 Zhang San                C003 English             Null null NULLS001 Zhang San C004 physical                              NULL NULL NULLS002 John Doe C004 physical       Null null NULLS003 Harry C004                             Physical NULL null NULLS004 Zhao Liu                             C001 math null null NULLS004 Zhao Liu                             C002 language null null NULLS004 Zhao Liu                             C003 English null null NULLS004 Zhao Liu C004 physical NULL NULL null (9 rows affected )--cross join: Specifies the cross product of two tables. ReturnBack to the same line-the cross join here is the Cartesian product select St.sno, st.name as Sname, Co.cno, co.name as CName from Students ST Cros s join Course CO--or not specify a connection condition, the query is also Cartesian product select St.sno, st.name as Sname, Co.cno, co.name as CName from S          Tudents St, Course co--or cross applyselect St.sno, st.name as Sname, Co.cno, co.name as CName from Students St The left and right operands of the cross apply Course CO/********************************apply**************************/--apply operator are table representations Expression The main difference between these operands is that right_table_source can use a table-valued function to get a column from Left_table_source as one of the parameters of the function. --left_table_source can include table-valued functions, but not as arguments from columns from Right_table_source.               --Check the highest scores of students in the two subjects select St.name, T.cno, t.score from Students ST cross apply (select top 2 * from score SC                            where St.sno = Sc.sno ORDER BY sc.score Desc) tname CNO                             Score-----------------------------------------------------------------------Zhang San C001 91 John Doe C003 95 John Doe                             C002 94 Harry C003 98 Harry  C002 97 (5 rows affected)--query all students with the highest scores of the two subjects, scores less than two subjects or no results, shown as empty select St.name, IsNull (T.cno, ' no ')               As course number, isnull (t.score,0) as score from Students ST outer apply (SELECT top 2 * from score SC                            where St.sno = Sc.sno ORDER BY sc.score Desc) Tname Course number                           Score-----------------------------------------------------------------------Zhang San C001                           91 John Doe C003 95 John Doe C002 94 Harry C003 98 Harry C00                       2    97 Zhao Six does not exist 0 (6 rows affected)/* In layman's words, cross apply is connected to a table or table-valued function, and the right table or representation function depends on The result set on the left is similar to traversing the left table on the outer layer, getting the data in the table on the left as a parameter, passing it to the query in the right (or function) as the query condition, and then the resulting set is connected to the result set of the outer, then the result is the cross apply and outer  The difference between apply is similar to whether the inner join and the left join determine whether the output is an issue in the case where there is no data in the right result set.

Jion operations in T-SQL