SQL Join Review

Here I just re-post a nice SQL JOIN diagram from stackoverflow by Lasse V. Karlsen, and adding a few examples to further illustrated the joins.

SqlJoin

Examples:

First let’s create two tables and insert some data.

CREATE TABLE table1 
(name varchar(50) not null, 
favoriteSports varchar(50))

INSERT into table1 
VALUES ('John','Soccer'),('Amy','Swimming'),('Ben','Skiing'),('Rob','Soccer'),('Mary','Swimming')

CREATE TABLE table2 
(name varchar(50) not null, 
favoriteColor varchar(50))

INSERT into table2 
VALUES ('John','Green'),('Dan','Green'),('David','Red'),('Mary','Yellow')

The tables now look like

Table1
name	favoriteSports
John	Soccer
Amy	Swimming
Ben	Skiing
Rob	Soccer
Mary	Swimming
Table2
name	favoriteColor
John	Green
Dan	Green
David	Red
Mary	Yellow

Next let’s run each of the join and see the query result. Note that the CASE expression can be replaced by COALESCE is T-SQL is used.

1. Left join

SELECT (CASE WHEN t1.name IS NOT NULL THEN t1.name ELSE t2.name END) AS name, favoriteSports, favoriteColor 
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.name=t2.name
name	favoriteSports	favoriteColor
John	Soccer	        Green
Amy	Swimming	NULL
Ben	Skiing	        NULL
Rob	Soccer	        NULL
Mary	Swimming	Yellow

2. Right join

SELECT (CASE WHEN t1.name IS NOT NULL THEN t1.name ELSE t2.name END) AS name, favoriteSports, favoriteColor 
FROM table1 t1
RIGHT OUTER JOIN table2 t2 ON t1.name=t2.name
name	favoriteSports	favoriteColor
John	Soccer	        Green
Dan	NULL	        Green
David	NULL	        Red
Mary	Swimming	Yellow

3. Full join

SELECT (CASE WHEN t1.name IS NOT NULL THEN t1.name ELSE t2.name END) AS name, favoriteSports, favoriteColor 
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.name=t2.name
name	favoriteSports	favoriteColor
John	Soccer	        Green
Amy	Swimming	NULL
Ben	Skiing	        NULL
Rob	Soccer	        NULL
Mary	Swimming	Yellow
Dan	NULL	        Green
David	NULL	        Red

4. Inner Join

SELECT (CASE WHEN t1.name IS NOT NULL THEN t1.name ELSE t2.name END) AS name, favoriteSports, favoriteColor 
FROM table1 t1
INNER JOIN table2 t2 ON t1.name=t2.name
name	favoriteSports	favoriteColor
John	Soccer	        Green
Mary	Swimming	Yellow

5. Left join variation

SELECT (CASE WHEN t1.name IS NOT NULL THEN t1.name ELSE t2.name END) AS name, favoriteSports, favoriteColor 
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON t1.name=t2.name
where t2.name IS NULL
name	favoriteSports	favoriteColor
Amy	Swimming	NULL
Ben	Skiing	        NULL
Rob	Soccer	        NULL

6. Right join variation

SELECT (CASE WHEN t1.name IS NOT NULL THEN t1.name ELSE t2.name END) AS name, favoriteSports, favoriteColor 
FROM table1 t1
RIGHT OUTER JOIN table2 t2 ON t1.name=t2.name
where t1.name IS NULL
name	favoriteSports	favoriteColor
Dan	NULL	        Green
David	NULL	        Red

7. Full join variation

SELECT (CASE WHEN t1.name IS NOT NULL THEN t1.name ELSE t2.name END) AS name, favoriteSports, favoriteColor 
FROM table1 t1
FULL OUTER JOIN table2 t2 ON t1.name=t2.name
where t1.name IS NULL or t2.name IS NULL
name	favoriteSports	favoriteColor
Amy	Swimming	NULL
Ben	Skiing	        NULL
Rob	Soccer	        NULL
Dan	NULL	        Green
David	NULL	        Red
Advertisements
This entry was posted in Database and tagged , . Bookmark the permalink.

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 )

Google+ photo

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

Connecting to %s