Summary of how joins are used in SQL Server

JOIN is divided into: Inner Connection ( INNER join), outer join (OUTER join). Among them, the outer joins are: left outer connection (OUTERjoin), right outer join (OUTER join), full outer join ( Full OUTER JOIN ), where the outer connection "OUTER" keywords can be omitted without writing.

1. Internal connection (shows the data that the left and right two tables can match exactly):

Select P.provinceid,p.provincename,c.cityname,c.provinceid,c.cityid
from [dbo]. [City] C inner JOIN [dbo]. [Province] P on p.provinceid = C.cityid where c.provinceid= 6

2. Left outer connection (displays all data on the left table, the right table does not match the display as NULL):

Select P.provinceid,p.provincename,c.cityname,c.provinceid,c.cityid
from [dbo]. [City] C left JOIN [dbo]. [Province] P on p.provinceid = C.cityid where c.provinceid= 6

The result is:

3. Right Outer connection (displays all data in the right table, the left table does not match the display as NULL):

Select P.provinceid,p.provincename,c.cityname,c.provinceid,c.cityid
from [dbo]. [Province] P right JOIN [dbo]. [City] C on p.provinceid = C.cityid where c.provinceid= 16

The result is:

4. Full outer connection (displays all data on both the left and right sides of the table, NULL if the two tables do not match ):

Select P.provinceid,p.provincename,c.cityname,c.provinceid,c.cityid
from [dbo]. [Province] P full OUTER JOIN [dbo]. [City] C on p.provinceid = C.cityid where c.provinceid= 6

The result is:

Summary of how joins are used in SQL Server