T-SQL JOINS


Yeah, I should know this by know. And I do. But occasionally I 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 joined with the matching rows in Y – repeats X for each Y found.
X LEFT JOIN Y
X LEFT OUTER JOIN Y
all the rows from X joined with the matching rows from Y – repeats X for each Y found.
– shows X even if no rows in Y match the X row.
X RIGHT JOIN Y
X RIGHT OUTER JOIN Y
all the rows from Y with the matching rows from X – repeats Y for each X found.
– shows Y even if no rows in X match the Y row.
X FULL JOIN Y
X FULL OUTER JOIN Y
all the rows from X with the matching rows from Y, and all the rows from Y with the matching rows from X, respectively (even if no row matches) – shows X even if no rows in Y match the X row.
– shows Y even if no rows in X match the Y row.
X CROSS JOIN Yall the rows from X joined with each row from Y– repeats X for each Y.
X UNION Yall the rows from X and Y as though they came from the same 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 )

Google photo

You are commenting using your Google 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