Monday, October 15, 2007

A Visual Explanation of SQL Joins

I thought Ligaya Turmelle's post on SQL joins was a great primer for novice developers. Since SQL joins appear to be set-based, the use of Venn diagrams to explain them seems, at first blush, to be a natural fit. However, like the commenters to her post, I found that the Venn diagrams didn't quite match the SQL join syntax reality in my testing.

I love the concept, though, so let's see if we can make it work. Assume we have the following two tables. Table A is on the left, and Table B is on the right. We'll populate them with four records each.

id name       id  name
-- ---- -- ----
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja

Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id name id name
-- ---- -- ----
1 Pirate 2 Pirate
3 Ninja 4 Ninja

Inner join produces only the set of records that match in both Table A and Table B.

Venn diagram of SQL inner join
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader

Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

Venn diagram of SQL cartesian join

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

Venn diagram of SQL left join
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

id name id name
-- ---- -- ----
2 Monkey null null
4 Spaghetti null null

To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.

join-left-outer.png
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

id name id name
-- ---- -- ----
2 Monkey null null
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader

To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

join-outer.png

There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram:

SELECT * FROM TableA
CROSS JOIN TableB

This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. If you do the math, you can see why this is a very dangerous join to run against large tables.

9 comments:

cutepig said...

I was very like to play the Rohan online game, in my mind I can gave up all things but I can not gave up the rohan crone, it gave me the courage and the confidence, in the game I was very like to earn the rohan gold, but I also need to buy rohan crone, but the same time I know a website sell the cheap rohan crone, so if you want to buy, I suggest you come here.

J&D said...

視訊|影音視訊聊天室|視訊聊天室|視訊交友|視訊聊天|視訊美女|視訊辣妹|免費視訊聊天室

自慰器|自慰器

網頁設計|網頁設計公司|最新消息|訪客留言|網站導覽

免費視訊聊天|辣妹視訊|視訊交友網|美女視訊|視訊交友|視訊交友90739|成人聊天室|視訊聊天室|視訊聊天|視訊聊天室|情色視訊|情人視訊網|視訊美女
一葉情貼圖片區|免費視訊聊天室|免費視訊|ut聊天室|聊天室|豆豆聊天室|尋夢園聊天室|聊天室尋夢園|影音視訊聊天室||

Peejay Li said...

Thank you for sharing such a nice article.
chaussures puma
puma speed cat
Nike Tn Chaussures
requin tn
nike shox
puma shoes
puma CAT
puma basket
puma speed
baskets puma
puma sport
puma femmes
puma shox r4 torch
nike air max requin
nike shox r3
shox rival r3
tn plus
chaussures shox
nike shox r4 torch
air max tn requin
nike tn femme
pas cher nike
tn chaussures
nike rift
nike shox nz
chaussures shox
nike shox rival
shox rival
chaussures requin
jeans online
cheap armani jeans
cheap G-star jeans

lucyliu said...

nike air max 90
nike air max 95
nike air max tn
nike air rift
nike shox r4
nike air max 360
nike shox nz
puma mens shoes
puma shoes
puma speed
nike shoes
nike air
nike air shoes
puma cat
air max trainers
mens nike air max
nike shoes air max
nike shoes shox
air shoes
nike shoe cart
puma future
cheap puma
sports shoes
nike air rifts
nike air rift trainer
nike air
nike rift
nike rift shoes
cheap nike air rifts
bape shoes
jeans shop
diesel jeans
levis jeans

Sneakers hobbies said...

nice post!!
I like ski,so a nice ski jackets is very important for me,spyder jackets is my favorite!!
If you want to buy wholesale polo shirts which are not only high quality polo shirts but also cheap polo shirts,you will interest in ralph lauren polo shirts

sports said...

wholesale
china wholesale
unlocked cell phones
wholesale cell phones
china phone

kids wall stickers
stickers for wall
wall sticker decal
wall stickers
round tablecloth
vinyl tablecloths
linens tablecloths
tablecloth
fashion bedding
bath and bed
bath curtains
kitchen faucet
bathroom faucet
faucet
shower faucet
baby bedding
bed in a bag
home bedding
kids bedding

wedding jewelry
wedding accessories
wedding dresses
cheap dress shoes
discount handbags
women's shoes
women's handbags
id lanyard
lanyard

unlocked cell phones
mp3 players
digital camera
digital picture frames
flat tv
lcd tv
hd tv
portable GPS
GPS navigation
bluetooth gps

travel luggage
camping tents
sleeping bag
car gps
car tires
car seat cover

sexual health
health plan

wholesale lots

Nike shox shoes said...

But it can make your feeling cool, so simple life we can enjoys it. No complecated, no hard, no knows make yourselves happy.

Nike shox shoes can surely not let you down,romance yourself with nike shox

happyoutlet said...

There are a wide variety -chi flat iron clothing stores for men, women and children, yarn shop, jewelry shops, gift shops, pharmacies, opticians, chi flat ironslibrary, and yes, even a department store. The only thing that consumers do not

missing is chi Straighteners ironsthe number of shopping centers and ongoing struggle for parking. But be sure to bundle, because it is cold. And do not forget your apartment. There are parking meters, so be sure not to miss

Time and food wise.

lee said...

If we are a variety of oil in some of these backwater places, these are precisely wherever these small seafood backside,Inches Farling mentioned"I'm air jordan shoes allayed I am living, nevertheless I am terrified for anyone whom have not been found still," he said, incorporating that she intentions to Air Jordan stay in San Felipe through the research in addition to expectation others remain living The ocean King, that Bill jigs within the U Air Jordan Cheap Photos released because of the Asian deep blue exhibited numerous sunburned anglers in T-shirts as well as Bermudas waiting to have over a Cheap Nike Air Max coach Individuals are tired now," Henry Scott mentioned Company spokesman Alan Jeffers said there is simply no direct link in between all those Nike Air Max troubles along with the pipe failureIn William placed the hands on his / her tummy and also joked in relation to pigging Air Max 2011 out for the getawayThe particular 20-year-old Silvertip pipe supplied 40,500 casks a day to a refinery throughout Billings coupled some sort of route Ed Hardy Shop of which moves underneath the lake