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:

(I don’t remember where I got this image from, so my apologies for not attributing the creator)

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 FROMGive meSpecial handling
Xall the rows in X
Yall 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 Yeach row from X joined with each row from Y, put together as XY rows– repeats each X for each Y.
X UNION Yall 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s