T-SQL JOINS

Yeah, I should know this by know. And I do. But occasionally I am so caught up in other things that I just can’t remember the different types and keep them straight. Here are two aids for times like those:

First, the following diagram:

Second, the following script gives a more concrete insight into the various joins.

DECLARE @tableX TABLE ( Xkey INT
                      , colA VARCHAR(10)
                      , colB VARCHAR(10)
                      );

DECLARE @tableY TABLE ( Ykey INT
                      , colC VARCHAR(10)
                      , colD VARCHAR(10)
                      );

INSERT INTO @tableX
     VALUES (1, 'Bob',  'in X & Y')
          , (2, 'Ted', 'in X only');

INSERT INTO @tableY
     VALUES (1, 'Carol', 'in X & Y')
          , (3, 'Alice', 'in Y only');
          
SELECT 'from X' [X]
     , [x].* 
  FROM @tableX [x];

SELECT 'from Y' [Y]
     , [y].* 
  FROM @tableY [y];

SELECT 'from X:' [X (INNER) JOIN Y]
     , [x].* 
     , 'from Y:' [ ]
     , [y].* 
  FROM @tableX [x]
       INNER JOIN @tableY [y]
               ON [x].[Xkey] = [y].[Ykey];

SELECT 'from X:' [X LEFT (OUTER) JOIN Y]
     , [x].* 
     , 'from Y:' [ ]
     , [y].* 
  FROM @tableX [x]
       LEFT OUTER JOIN @tableY [y]
               ON [x].[Xkey] = [y].[Ykey];

SELECT 'from X:' [X RIGHT (OUTER) JOIN Y]
     , [x].* 
     , 'from Y:' [ ]
     , [y].* 
  FROM @tableX [x]
       RIGHT OUTER JOIN @tableY [y]
               ON [x].[Xkey] = [y].[Ykey];

SELECT 'from X:' [X FULL (OUTER) JOIN Y]
     , [x].* 
     , 'from Y:' [ ]
     , [y].* 
  FROM @tableX [x]
       FULL OUTER JOIN @tableY [y]
               ON [x].[Xkey] = [y].[Ykey];

SELECT 'from X:' [X CROSS JOIN Y]
     , [x].* 
     , 'from Y:' [ ]
     , [y].* 
  FROM @tableX [x]
       CROSS JOIN @tableY [y]
 ORDER BY [x].[Xkey], [y].[Ykey];

 SELECT 'came from X:' AS [X UNION Y]
      , [x].[Xkey]     AS [Xkey_U_Ykey]
      , [x].[colA]     AS [colA_U_colC]
      , [x].[colB]     AS [colB_U_colD]
   FROM @tableX [x]
  UNION SELECT 'came from Y:' AS [X UNION Y] 
             , [y].[Ykey]     AS [Xkey_U_Ykey]
             , [y].[colC]     AS [colA_U_colC]
             , [y].[colD]     AS [colB_U_colD]
          FROM @tableY [y];

Executing the above script should give these results:

(note: Select keywords in (parentheses) are optional)

And finally, here is a natural language interpretation:

SELECT FROM	Give me	Special handling
X	all the rows in X
Y	all the rows in Y
X JOIN Y X INNER JOIN Y	all the rows from X that can be joined with rows in Y, put together as XY rows	– repeats the X portion for each matching Y found. – skips the X row if no rows in Y match the X row.
X LEFT JOIN Y X LEFT OUTER JOIN Y	all the rows from X, with rows from Y that can be joined, put together as XY rows	– each X row may join with multiple Y rows. each combination will be a separate XY with the same X. – shows an XY even if no rows in Y were joined to the X row (Y portion is NULLs).
X RIGHT JOIN Y X RIGHT OUTER JOIN Y	all the rows from Y, with the rows in X that can join to them, put together as XY rows	– each Y row may be joined to multiple X rows. each combination will be a separate XY with the same Y. – shows an XY even if no rows in X joined to the Y row (X portion is NULLs). – (note: this is equivalent to Y LEFT JOIN X).
X FULL JOIN Y X FULL OUTER JOIN Y	all the rows from X, with the rows in Y that can be joined, and all remaining rows in Y, put together as XY rows	– each X row may join with multiple Y rows. each combination will be a separate XY with the same X. – shows an XY even if no rows in Y were joined to the X row (Y portion is NULLs). – shows an XY even if no rows in X joined to the Y row (X portion is NULLs).
X CROSS JOIN Y	each row from X joined with each row from Y, put together as XY rows	– repeats each X for each Y.
X UNION Y	all the rows from X, plus all the rows from Y, as though they came from the same X table	– column names default to columns from X. – X and Y must have the same column formats.