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.

Key	Value	Key	Value
AK	Alaska	NULL	NULL
AL	Alabama	NULL	NULL
AZ	Arizona	NULL	NULL
CA	California	NULL	NULL
CO	Colorado	CO	Colorado
WA	Washington	WA	Washington

Key	Value	Key	Value
CO	Colorado	CO	Colorado
WA	Washington	WA	Washington

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

Key	Value	Key	Value
NULL	NULL	AR	Arkansas
CO	Colorado	CO	Colorado
NULL	NULL	CT	Connecticut
NULL	NULL	DE	Delaware
NULL	NULL	FL	Florida
WA	Washington	WA	Washington

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

Key	Value	Key	Value
CO	Colorado	CO	Colorado
WA	Washington	WA	Washington

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

Key	Value	Key	Value
CO	Colorado	CO	Colorado
WA	Washington	WA	Washington
Key	Value	Key	Value
AK	Alaska	NULL	NULL
AL	Alabama	NULL	NULL
AZ	Arizona	NULL	NULL
CA	California	NULL	NULL
CO	Colorado	CO	Colorado
WA	Washington	WA	Washington
NULL	NULL	AR	Arkansas
NULL	NULL	CT	Connecticut
NULL	NULL	DE	Delaware
NULL	NULL	FL	Florida