SQL Join Diagram and Query

SQL Join Diagram and Query

Create TableA and TableB and Populate Data

CREATE TABLE TableA
    (
      ID INT IDENTITY(1, 1) ,
      [Key] NVARCHAR(255) ,
      Value NVARCHAR(4000) ,
      CreatedDateTime DATETIME2 DEFAULT GETUTCDATE() ,
      CreatedBy NVARCHAR(50) DEFAULT CURRENT_USER ,
      Status TINYINT DEFAULT 1
    );

CREATE TABLE TableB
    (
      ID INT IDENTITY(1, 1) ,
      [Key] NVARCHAR(255) ,
      Value NVARCHAR(4000) ,
      CreatedDateTime DATETIME2 DEFAULT GETUTCDATE() ,
      CreatedBy NVARCHAR(50) DEFAULT CURRENT_USER ,
      Status TINYINT DEFAULT 1
    );


INSERT  INTO dbo.TableA
        ( [Key] ,
          Value
        )
        SELECT  'AL' ,
                'Alabama'
        UNION
        SELECT  'AK' ,
                'Alaska'
        UNION
        SELECT  'AZ' ,
                'Arizona'
        UNION
        SELECT  'CA' ,
                'California'
        UNION
        SELECT  'CO' ,
                'Colorado'
        UNION
        SELECT  'WA' ,
                'Washington';


INSERT  INTO dbo.TableB
        ( [Key] ,
          Value
        )
        SELECT  'FL' ,
                'Florida'
        UNION
        SELECT  'DE' ,
                'Delaware'
        UNION
        SELECT  'CT' ,
                'Connecticut'
        UNION
        SELECT  'AR' ,
                'Arkansas'
        UNION
        SELECT  'CO' ,
                'Colorado'
        UNION
        SELECT  'WA' ,
                'Washington';

LEFT JOIN : Select records from the first (left-most) table with matching right table records.

SELECT  A.[Key] ,
        A.Value ,
        B.[Key] ,
        B.Value
FROM    dbo.TableA A
        LEFT JOIN dbo.TableB B ON A.[Key] = B.[Key];

SELECT  A.[Key] ,
        A.Value ,
        B.[Key] ,
        B.Value
FROM    dbo.TableA A
        LEFT JOIN dbo.TableB B ON A.[Key] = B.[Key]
		WHERE B.[Key] IS NULL

RIGHT JOIN : Select records from the second (right-most) table with matching left table records.

SELECT  A.[Key] ,
        A.Value ,
        B.[Key] ,
        B.Value
FROM    dbo.TableA A
        RIGHT JOIN dbo.TableB B ON A.[Key] = B.[Key];

SELECT  A.[Key] ,
        A.Value ,
        B.[Key] ,
        B.Value
FROM    dbo.TableA A
        RIGHT JOIN dbo.TableB B ON A.[Key] = B.[Key]
		WHERE A.[Key] IS NOT NULL

INNER JOIN : Select records that have matching values in both tables.

SELECT  A.[Key] ,
        A.Value ,
        B.[Key] ,
        B.Value
FROM    dbo.TableA A
         JOIN dbo.TableB B ON A.[Key] = B.[Key]

FULL OUTER JOIN : Selects all records that match either left or right table records.

SELECT  A.[Key] ,
        A.Value ,
        B.[Key] ,
        B.Value
FROM    dbo.TableA A
         FULL OUTER JOIN dbo.TableB B ON A.[Key] = B.[Key]

Results from Above Queries in Order

Left Join Results – Select records from the first (left-most) table with matching right table records.

Right Join Results – Select records from the second (right-most) table with matching left table records.

Inner Join Results – Select records that have matching values in both tables.

Full Outer Join Results – Selects all records that match either left or right table records.