sql 的join 经典

right

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77),



TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')



SELECT Roster.LastName  , TeamMascot.Mascot ,Roster.SchoolID as Rs,TeamMascot.SchoolID as Ts
FROM Roster  right JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

left

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77),



TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')



SELECT Roster.LastName  , TeamMascot.Mascot ,Roster.SchoolID as Rs,TeamMascot.SchoolID as Ts
FROM Roster  left JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;

inner

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77),



TeamMascot AS
 (SELECT 50 as SchoolID, 'Jaguars' as Mascot UNION ALL
  SELECT 51, 'Knights' UNION ALL
  SELECT 52, 'Lakers' UNION ALL
  SELECT 53, 'Mustangs')



SELECT Roster.LastName  , TeamMascot.Mascot ,Roster.SchoolID as Rs,TeamMascot.SchoolID as Ts
FROM Roster  inner JOIN TeamMascot ON Roster.SchoolID = TeamMascot.SchoolID;