Outer JOIN usage in SQL Server 2005 _mssql2005

Outer JOIN usage in SQL Server 2005 one:

There are two tables: Student table, student selection timetable, the table data are:

Student table:

Students choose the timetable:

To view all students ' elective information,

The standard SQL statement, the OUTER JOIN statement is

Use Stu_course
Select L.sno,sname,ssex,sage,sdept,cno,grade from
student L,sc
where L.SNO=SC.SNO (*)

The theoretical results are:

However, an error occurs when running in SQLServer2005, and the error message is:

Message 102, Level 15, State 1, line 4th
There is a grammatical error near the ' * '.

In SQLServer2005, the corresponding T-SQL statement should be:

Use Stu_course
Select L.sno,sname,ssex,sage,sdept,cno,grade from
student L-LEFT join SC on
L.sno=sc.sno

The results of the operation are:

At the same time, you can add the where qualification after on:

Use Stu_course
Select L.sno,sname,ssex,sage,sdept,cno,grade from
student L-LEFT join SC on
L.sno=sc.sno
where L.sno in (95001,95002,95003,95004)

At this point, the results of the operation are:

The above example is a LEFT outer join query, that is, the result table contains all the first tables that meet the criteria

Records, if there is a record in the second table that satisfies the join condition, the corresponding value is returned, or null is returned.

Outer JOIN query usage in SQLSERVER2005 (cont.):

There are three tables: Student table, students ' timetable, timetable, and the data in the table are:

Student table:

Students choose the timetable:

Curriculum:

Left JOIN query For example:

Use Stu_course
Select L.sno,sname,ssex,sage,sdept,cno,grade from
student L-LEFT join SC on
L.sno=sc.sno

The results are:

Right Join query Example:

Use Stu_course
select Sno,course.cno,course.cname from
SC Right join course on
sc.cno=course.cno

The results are:

An example of a full outer join query:

Use Stu_course
select Sno,course.cno,course.cname from
SC full join course on
sc.cno=course.cno

The results are:

If you exchange the order of the left and right two tables in this entire outreach:

Use Stu_course
Select Sno,course.cno,course.cname from
course full join SC on
sc.cno=course.cno

The result is still:

As can be seen from the above examples:

For a left outer join, the resulting table contains all the records in the first table that meet the criteria, and if there are records in the second table that satisfy the join condition, the corresponding value is returned, otherwise null is returned;

For a right outer join, the resulting table contains all the records in the second table that meet the criteria, and if there are records in the first table that satisfy the join condition, the corresponding value is returned, otherwise null is returned;

       for all outer joins, the resulting table contains all records in two tables that meet the criteria, and returns the corresponding value if there are records in one of the tables that satisfy the join condition;