SQL SERVER left JOIN, INNER join, right Join

• JOIN: Returns a row if there is at least one match in the table
• Left JOIN: Returns all rows from the table, even if there is no match in the right table
• Right JOIN: Returns all rows from the correct table even if there is no match in the left table
• Full JOIN: Returns a row if there is a match in one of the tables

CREATE TABLE dbo. Student (Sno int  null,name nvarchar) CREATE TABLE dbo. Score (Sno int, score int) INSERT into dbo. Student (Sno,name) VALUES (1, ' Jesse '); INSERT into dbo. Student (Sno,name) VALUES (2, ' Jessca '); INSERT into dbo. Student (Sno,name) VALUES (3, ' June '); INSERT into dbo. Student (Sno,name) VALUES (4, ' Supper '); INSERT into dbo. Score (Sno,score) VALUES (1,45) INSERT into dbo. Score (Sno,score) VALUES (1,56) INSERT into dbo. Score (Sno,score) VALUES (2,100) INSERT into dbo. Score (Sno,score) VALUES (3,25)  

View data:

SELECT * FROM dbo. Studentselect * FROM dbo. Score

　　

The left Join displays all the data on the right, and the system defaults to NULL if there are no corresponding values.

SELECT *from dbo. Student aleft JOIN dbo. Score BON A.sno=b.sno

　　

INNER JOIN Display Intersection

SELECT *from dbo. Student Ainner JOIN dbo. Score BON A.sno=b.sno

　　

Right join all display, left table not, default set to NULL

SELECT *from dbo. Student aright JOIN dbo. Score BON A.sno=b.sno

　　

Full JOIN is displayed on the left and right side, if the other side is not worth the uniform setting null

SELECT *from dbo. Student afull JOIN dbo. Score BON A.sno=b.sno

　　

SQL SERVER left JOIN, INNER join, right Join