SQL Join Diagram and Query
SQL Join Diagram
Create TableA and TableB and Populate Data
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 |
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 |
Loading...
Leave a Reply