Coding Horror: A Visual Explanation of SQL Joins

1 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

programmingandhumanfactors
byJeAtwood

AVisualExplana onofSQLJoins
October11,2007
IthoughtLigayaTurmelle'spostonSQLjoinswasagreatprimerfornovicedevelopers.SinceSQLjoinsappeartobesetbased,theuseof
Venndiagramstoexplainthemseems,atfirstblush,tobeanaturalfit.However,likethecommenterstoherpost,IfoundthattheVenn
diagramsdidn'tquitematchtheSQLjoinsyntaxrealityinmytes ng.
Ilovetheconcept,though,solet'sseeifwecanmakeitwork.Assumewehavethefollowingtwotables.TableAisonthele ,andTableB
isontheright.We'llpopulatethemwithfourrecordseach.
id
-1
2
3
4

name
---Pirate
Monkey
Ninja
Spaghetti

id
-1
2
3
4

name
---Rutabaga
Pirate
Darth Vader
Ninja

Let'sjointhesetablesbythenamefieldinafewdierentwaysandseeifwecangetaconceptualmatchtothoseni yVenndiagrams.

SELECT * FROM TableA

INNER JOIN TableB
ON TableA.name = TableB.name
id
-1
3

name
---Pirate
Ninja

id
-2
4

name
---Pirate
Ninja

Innerjoinproducesonlythesetofrecordsthatmatch
inbothTableAandTableB.

SELECT * FROM TableA

FULL OUTER JOIN TableB
ON TableA.name = TableB.name
id
-1
2
3
4
null
null

name
---Pirate
Monkey
Ninja
Spaghetti
null
null

id
-2
null
4
null
1
3

name
---Pirate
null
Ninja
null
Rutabaga
Darth Vader

FullouterjoinproducesthesetofallrecordsinTableA
andTableB,withmatchingrecordsfrombothsides
whereavailable.Ifthereisnomatch,themissingside
willcontainnull.

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

2 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

SELECT * FROM TableA

LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
id
-1
2
3
4

name
---Pirate
Monkey
Ninja
Spaghetti

id
-2
null
4
null

name
---Pirate
null
Ninja
null

Le outerjoinproducesacompletesetofrecordsfrom
TableA,withthematchingrecords(whereavailable)in
TableB.Ifthereisnomatch,therightsidewillcontain
null.

SELECT * FROM TableA

LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null
id
-2
4

name
---Monkey
Spaghetti

id
-null
null

name
---null
null

ToproducethesetofrecordsonlyinTableA,butnotin
TableB,weperformthesamele outerjoin,then
excludetherecordswedon'twantfromtherightside
viaawhereclause.

SELECT * FROM TableA

FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null
id
-2
4
null
null

name
---Monkey
Spaghetti
null
null

id
-null
null
1
3

name
---null
null
Rutabaga
Darth Vader

ToproducethesetofrecordsuniquetoTableAand
TableB,weperformthesamefullouterjoin,then
excludetherecordswedon'twantfrombothsidesvia
awhereclause.

There'salsoacartesianproductorcrossjoin,whichasfarasIcantell,can'tbeexpressedasaVenndiagram:
SELECT * FROM TableA
CROSS JOIN TableB

Thisjoins"everythingtoeverything",resul ngin4x4=16rows,farmorethanwehadintheoriginalsets.Ifyoudothemath,youcansee
whythisisaverydangerousjointorunagainstlargetables.

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

3 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

PostedbyJeAtwood
Relatedposts:
AllAbstrac onsAreFailedAbstrac ons
Deadlocked!
GivemeparameterizedSQL,orgivemedeath
CompiledorBust?
StoredProceduresvs.AdHocSQL

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

4 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

190Comments
Funny,Ijustexplainedthistoacoworkerinthesamemannerearlierintheweek.Iguessitneverdawnedonmethatothersmayhave
neverthoughtaboutjoinsintermsofthesediagrams.Goodpost,Je!
ShawnWheatleyonOctober12,20072:14AM

HeyJe,thanksforthegreatblogIthoroughlyenjoyreadingeverysingleoneofyourposts.
EventhoughIo enamfamiliarwiththeconceptsyoutalkof,Ifindthesimplemannerinwhichyoubreakdowntheissuesisalwaysa
greatrefresher.
Keepupthegreatwork.
capdogonOctober12,20072:32AM

linkedfromjoin(SQL)enwikipediatoyourblogentry...yourpostismuchbe er(orsimplis c)thanthetechtalkonthatwikipediaentry

;)
hack ckonOctober12,20073:00AM

ThepostismuchsimplerthantheWikipediaentrybecauseitomitsallthenontrivialcases.Alloftheseexamplesassumethateach
entryintableAwillbematchedbythejoinpredicatetoatmostoneentryintableB,andviceversa.
WeebleonOctober12,20073:35AM

I'mnotevensophis catedenoughtousedatabasesanymore(mycareerasadeveloperhasdevolved),butwhenIwas,99%ofthe meI

usedinnerjoinsandjustthoughtofitasthedatacommontobothtables.
EverynowandthenIwantedsomedatathatdidn'tfitthatkindofjoinandI'ddoale outerjointogetthedatathatwas"le out".
BradonOctober12,20073:47AM

SpeakingaboutOracle(+)syntaxandLEFT/RIGHT[OUTER]JOINsyntaxthereisadierence.Anditisnicelydescribedhere
h p://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondi on.html
SpeakingaboutANSIsyntaxwhereeveryonehastoexplicitlysaytomakeCROSSJOINtogetCartesianproductatleastinOracleitisnot
true,usingstupid(atleasttomymind)NATURALJOINclauseonecaneasilygetCROSSJOINandalsoJOINonsuchcolumnshenever
thoughtitreallyis.I'vebloggedaboutithere
h p://gplivna.blogspot.com/2007/10/naturaljoinsareevilmo oifyou.html
Don'tknowwehetheritisjustOraclespecificorasperANSISQLstandard.
SpeakingaboutINNNERJOINSasaCartesianproductandthenelimina ngallunnecessaryrows,atleasttherealmechanicsinOracleis
absolutelydierent,therearefollowingpossibili esfordoingthem:
NESTEDLOOPS(OUTER)
HASHJOIN(OUTER)
MERGEJOIN(OUTER)
andforCartesianproductitusesMERGEJOINCARTESIAN,andthat'swhenusuallytherealtrouble(fillingtemp)starts;)
TherealchoiceamongNestedloops,hashjoinormergejoinatleastdependsondata,sta s cs,availablememoryforasession,
explicitlygivenavailablememoryforhashjoinsormergejoins(btwthereispossibilitytogiveforonebutnotforother),system
workload,hints,ini aliza onparametersallowingceratainjointypes(atleastforhashjoins)andprobablysomethingother:)

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

5 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

GintsPlivnaonOctober12,20073:51AM

HiJe,
Ithinkyourillustra onsmakeoneBIGassump on:TableAandTableBcontainuniquerows.Ifeithertablecontainedduplicate
matchingrecords,thenthewholeVenndiagramwillnothold(thecardinalityofthesetswillnotaddup).Iamafraidthisvisual
explana onwillleadsometothinkthatyoucanuseSQLjoinstodofilteropera onswhichshouldn'tbedonethisway.
Althoughit'snicea empttoexplainSQLjoins,overallIthinkitismisleading.
dt
dtonOctober12,20073:57AM

OneoftheworstthreemonthsofmyCStrainingwasaclassostensiblyondatabasetheorywhichwasreallyabootcampinSQLsyntax,
taughtbyaJohnnyonenoteprofessorwhothoughtSQLandRDMSswerethegreatestinven onofmankind.
Thisexperienceturnedmeodatabasesfor15yearsun lonedayintheshowerIrealizedthatjoinswereanalogoustosettheory,ina
roughway.(Yestheyare,allyounaysayers!"Givemeeverythingthat'sthereANDthere"or"Givemeeverythingthat'sthereORthere"
maynothandleallthepossibleinputs,butit'sagoodjumpingopointforexplaining"inner"and"outer."Andwhocameupwiththat
stupid,arbitraryterminologyanyway?)
Is llthinkSQLisanawfullanguage,though,andrela onaldatabasesareanuglyhackmandatedbyhardwarelimita onstrumping
elegantdesign.OLAP"cubes"aresoclearlyabe ersolu onsointui velyclearandobviousthenaturalgeneraliza ontohigher
dimensionsoftheflatfiledatabase.
AlexChamberlainonOctober12,20074:09AM

Tome,aCartesianProductwouldbebestillustratedbybothcirclesbeingblank.
Granted,alltheSQLguruswillprobablyspitfeathersatmyoutrageoussugges on,butforme,it'sabeau fullysimplemethodof
explainingsimplejoinsandI'llbeusingitinmetechnicaldocumenta on!
Thanksforthisar cle,Je.
KevinonOctober12,20074:12AM

Je,youare_the_masterofwellcra edblogpoststhatsome mesdon'tsaymuch,butsomehowgatherscadsofinanecomments!This

oneandthepreviouspost(onexercise)aregreatexamples.
Now,notallofyourpostsareinthiscategory,thankGod!ButI'mwatchingyou...
SteveonOctober12,20074:25AM

HeyNowJe,
Thesediagramsmakemethinkofwhenlearningofclassicbooleanlogic.
CodingHorrorFan,
Ca o
Ca oonOctober12,20074:37AM

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

6 de 19

Well,thedatabaseprac

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

onerrejoice,andthesetrela onistscringe.

Thereisonlyonejoin,whichisthecrossproduct(eachrowofthefirsttableconcatenatedtoeachrowofthesecondtable).TheINNER
JOINexampleonlydiscardssomerowsbythewhereclause,otherwiseitis(orshouldbe)thesameastheCROSSJOIN.Theouterjoinisa
hackthatisn'tveryfirmlyrootedinrela onalalgebra.
AndreasKreyonOctober12,20074:47AM

NiceJe,thankyousomuchforthispost!
IalwaysgotconfusedwithJOINSandhowtheywork,that'soneofthesethingsthatarehardtobeexpainedinabookoramanual.
Butthesediagramsmakeitpre yclear,theyaregreat!
jan.gonOctober12,20075:10AM

Don'tforgetEXCEPT!Whichwas(andaddi onallys llis)MINUSinOracle.ForyearsIthinkOraclewastheonlyRDBMSthat

implementeditandIhad,innearly20years,useditallof,oh,once.Un llastweek,whenIuseditthesecond me.
It'sthesameasyourfourthexample:insteadof
SELECT*FROMTableA
LEFTOUTERJOINTableB
ONTableA.name=TableB.name
WHERETableB.idISnull
wecansay
SELECT*FROMTableA
MINUS
SELECT*FROMTableB
(IusedtheoldOracleoperatorit'smoreexpressivetomyeyes)
Noteverypla ormimplementstheoperatoryet:MySQL(whichIjustchecked)doesn't,forexample.MSSQLServer2005does.SQLIte?
Hangon...yup,thatworksinv3atleast.
MikeWoodhouseonOctober12,20075:21AM

Verystrange.Inmy"DatabaseManagement"classatArizonaStateUniversityastudentaskedaboutJoin'sandmyprofessorsaid"I
neverusethem.Justs ckwiththeWHEREsyntax".
Thiswasamoretheore calcoursethough.WespentFARmore meinrela onalalgebra/calculusandalgorithmsforensuringatomic
transac onsthanwedidinmoreprac calstulikedatabasenormaliza on.
Hmm...lotsofpeoplesaythetheore calstuisawasteof me.Forme,well,IalmostbecameamathmajorinsteadofCS,soIfindthe
theore calstumoreinteres ng.
KGonOctober12,20075:21AM

Ifyoudon'tuseranyjoinfeatureandjustdo"select*fromTableA,TableB",youhaveyourcrossjoin.

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

7 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

joskeonOctober12,20075:26AM

Venndiagrams.
wow,sosimple,soobvious;howcomeIhaven'tseenthisanywhereelse?
davidonOctober12,20075:42AM

HiJe
that'sannicediagramandanewwayofpresen ngthesqljoinfunc on.
RockonOctober12,20075:47AM

Yetanothercaseoftheblindleadingtheblind(downtheblindalleyofignoranceandarrogance);theoriginal,BTW.Atleastyoumade
anda empttofixitup.Butreadingallthose"nowIgetit"comments,makesitclearerwhyYahooslikeGWBushgetawaywithbald
facedlies."Peoplebelievewhattheywanttobelieve,whenitmakesnosenseatall"/Mellencamp.
Nowwe'llhaveanothergaggleoffolksrunningaroundfawningonAmblerandFowler.Gad.TheHorror,theHorror.
BuggyFunBunnyonOctober12,20076:22AM

ThankstothisI'mnowskippingmerrilydownthepathofBOMmanagementandstockcontrol,ratherthanflickingfuriouslythrough
Teachyourselfbooks,thanks
ChrisonOctober12,20076:36AM

Ilikeit
thanksforyourwork.
cocobearonOctober12,20076:42AM

Excellentpost.
Everyonewhohasfollowed(andpassed)acomputersciencebeginnercourseshouldbeabletounderstandthis.
RasmusonOctober12,20076:45AM

It'simportanttonotethatwhenjoiningtwotablesonnonindexedcolumnsmostdatabasesperformtheequivalentofacrossjoinand
thenweedouttherowsthatdon'tmatchthejoin/wherecriteria.Whilethisdoesntreallyimpactthequeryresults,ithasabigimpact
onqueryperformance.
InMySqlyoucanrun:explainmyquerytodetermineifthequeryisusingindexesandreviewperformanceofallyourqueries.
CRonOctober12,20076:59AM

Perfect ming.IjusthadanewprojectthrownonmydeskthatwillrequireworkingwithSQLsomethingIknowli leaboutandhave

avoided.Termsthathaveglazedmyeyesinweekspastsuddenlymakesense.

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

8 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

WombatonOctober12,20077:13AM

dtis100%right.Youreallyneedadisclaimerthatindicatesthatyoursetdiagramsonlyholdwhenthereisatbestaonetoone
correspondencebetweenjoincriteriavaluesinthetables.
Thesecondyouhavetwoorthreepiratesinoneofthetables(an_extremely_commonrealworldscenario,probablymuchmore
commonthanthesimplifiedcaseyoushowhere)yourVenndiagramsbreakdown.
Ma WigdahlonOctober12,20077:29AM

WhatagreatwaytoexplainJOINs,I'mcertaintherearethousandsofstudentsthatwouldbenefitfromthis...apictureistrulywortha
thousandwords(especiallyincaseslikethis).
ThomasBorzeckionOctober12,20077:38AM

Ialwaysusele outerjoinsandneverreallylikedtherightouterjoin.Isthereany metheyareuseful?Italwaysseemsbassackwardsto

usearightouterjoin.
Ofcourseotherdevelopersusevisualquerydesignerswhichuserightouterjoins...
JoeBeamonOctober12,20077:39AM

IsitjustmeorOracle'ssyntaxismuchsimplerthanthat?Like:
select*fromTableA,TableB
whereTableA.Name=TableB.Name
I'mfamiliarwithboth,bothIalwayslikedthatonebe er.
RicardoonOctober12,20077:48AM

Je,
ThisisgreatforthoseSQLtypesthatdidn'ttakeasettheory,rela onalcalculusorevendiscretemathincollege.
I'dlovetoseeyouvisualizeacrossproduct:)
JayR.WrenonOctober12,20077:57AM

Ricardo,thatsyntaxispreANSI.Mostdatabasess llsupportitbuttheadvantagesoftheINNERJOIN,etc.syntaxare:itmakesthe
queriesmoreportableacrossdatabases;itallowssepara onofthejoincriteriafromthefiltercriteria,whichimprovesreadability;and
allowsfullouterjoinswithoutsyntac chacks.
AnotherhugebenefitisthatifyouwanttodoaCROSS(Cartesian)JOIN,andusuallyyoudon't,youhavetocallitoutspecifically.Inthe
oldsyntax,ifyouscreweduptheWHEREclauseyoucouldgetwildlyhugeresultsetsduetoinadvertentCartesianjoins.
Ma WigdahlonOctober12,20078:02AM

JoeBeam,

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

9 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

TheonlyuseIhavefoundforRIGHTOUTERiswheni'mtoolazytoreordermyquery.;)Otherthenthatitmakesmoresensetouseonly
onetype.
IuseLEFTJOINtoo,asdomycompany.
IagreewithotherresponseshereJe,whileit'sareallynicesimplewaytorepresentjoins,itdoesn'thandlemanytomanyoroneto
manyverywellandlet'snottalkaboutmalformedjoins.
It'sagoodprimer,butshouldperhapscontainawarning.
EdKennyonOctober12,20078:08AM

Thecommenterspoin ngoutthatthediagramsbreakdownincaseofmul pleandorduplicateresults,areabsolutelyright.Iwas

actuallythinkingofjoinsalongtheprimarykey,whichtendstobeuniquebydefini on,althoughtheexamplesarenotexpressedthat
way.
Likethecartesianorcrossproduct,anythingthatresultsinmorerowsthanyouoriginallystartedwithdoesabsolutelybreaksthewhole
venndiagramconcept.Sokeepthatinmind.
JeAtwoodonOctober12,20078:11AM

Exceptthatvenndiagramsexplainsetlogic,andSQLJoinshaveveryli letodowithsetlogic.
Scaryhowmanypeopleareagreeingwiththis.
Wya onOctober12,20078:29AM

Actually,I'vealwaysbeenabitshakyonjoinsmyself.Ialwayshavetolookthemupany meIneedtousingajointhatisn'tastandard
filteredcrossproduct.Butseeingthisvisualguidehasreallyhelpedmetograspitmuchbe er.IjustwentbackandreadtheWikipedia
entryforjoinsanditmakescompletesensetomenow.Beforeseeingthisvisualrepresenta onImighthavejustglazedoverwhen
readingtheWikipediaar cle.
Soeveniftheyaren'texactlyrighthavingavisualrepresenta onhasreallyhelpedmetounderstandwhatisgoingon.
Thanks!
Ma onOctober12,20078:29AM

AnicetrickisyoucanwritearbitraryfiltersintheONclauseofthejoin.Iusethiswhenwri ngtrulyawfuljoinsorwhenIneedthele
joinwithanalreadyfilteredtable.
JoshuaonOctober12,20079:17AM

FromoneJetoanother,thisisabrilliantessayonasimpletopicsimplebutvexingtonewbies.IamaDB2DBA,andexplainingouter
joinstothenewbiedevelopersIencounterconstantlywillbemucheasierwithmaterialslikethis.Thanks,keepitcoming.
JereyBenneronOctober12,20079:25AM

Ihaveneverseensuchagoodillustra onofSQLjoins.Goodwork.

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

10 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

JeonOctober12,20079:28AM

Imustagreethatwhilethediagramsareafairlygoodillustra onsofwhathappenswithajoin,they'renotVenndiagramsinthestrict
sensebecausethat'sjustnotwhat'shappening.WhatthefirstdiagramsaystomeisthatyougetallrecordsthatarebothinsetAandset
B(akaintersec on).That'sjustnottrue.Yougetasetofnewrecords,thatareneitherinsetAnorinsetB,buttheycontaina
combina onofthea ributesofbothsets.
AaronG:joinsareconceptuallyaspecialcaseofthecartesianproduct.Howthedatabaseserveractuallycomputesthemisanother
ma eren rely.
Conceptually,inrela onalalgebra,aninnerjoinisacrossproductfollowedbyaselec ononthejoincondi on.That'saterribly
inecientwaytocomputeit,butconceptually,that'swhatitis.
SvenGrootonOctober12,200710:09AM

ThemoronsrunningmydatabasefundamentalsclassatmyuniversitythoughtthatteachingtheCartesianproductasamethodof
joining,thenfilteringtheresults,wasavalidalterna vetotheinsanelysimpleinnerjoin.Somanypoorstudentsaregoingtohavea
hard meintheindustryasaresult.
LachlanMcDonOctober12,200710:21AM

JonRaynor:
Outofcuriosity,butwhatdatabasesotherthanOraclesupport+forjoins?
Anyway,Ipreferthe"TableAaINNERJOINTableBbONa.something=b.somethingelse"syntaxover"TableAa,TableBbWHERE
a.something=b.somethingelse"
(MostDBsalsosupport"USING(something)"insteadof"ONa.something=b.something"ifthecolumnshavethesamename)
ManyDBswillthrowanerrorifIaccidentlyomittheONstatementinsteadofgivingmeacrossjointhatIdidn'twant.Iimaginethatthe
queryparserwouldalsoparseitfaster,asIspecifyupfrontwhichtypeofjoinI'mdoing;thequeryparserdoesn'thavetofigureitout.
PowerlordonOctober12,200710:31AM

Je,
Ilikehowyoucanregurgitateanotherauthor'scontent,andgetawaywithit.
PhilihponOctober12,200710:45AM

Bigdeal,SQLfinallycaughtupwiththe1980's.UsedaveryfineDBMSandlanguage,NOMAD2,onmainframesthatdidthisstubut
onlymuchbe erandmorethoroughly.
SteveonOctober12,200710:58AM

nicelydone.ThoughIdohavetoadmittofindingithumorousthatwhatstartedoutasafast,dirtywaytotrytoclarifytosomeone(on
anIRCchannel)whytheywerege ngthewronginforma onforajoinnowhasotherscorrec ngandlinkingtome.
LigayaTurmelleonOctober12,200710:59AM

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

11 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

Bindun!
h p://www.khankennels.com/blog/index.php/archives/2007/04/20/ge ngjoins/
Nicepostthough;)
BarryonOctober12,200711:04AM

"IwasundertheimpressionthatINNERJOINsuseahashmatchinternally(infact,I'mquitecertainofthis).CROSSJOINsobviouslydon't.
ThereforeIdon'tseehowit'sreasonabletosaythatanIJisjustaspecialcaseoftheCJ.Isupposeinbreadandbu ersettheory,where
there'snono onofahashoranindex,thiscouldwork,butthisistheworldofCS,wherethecleanacademicsolu onisalmostnever
themostprac calone.TheIJyouspeakofisthenaivestpossiblealgorithm,onlyusedwhentherearenoindexes(andifyou're
designingdatabaseswithoutanyindexes,well...stopdesigningdatabases)."AaronG
AnIJisdefinedasbeingtheequivalentofafilteredCJ.Thefactthatthiswouldnotbeareasonableimplementa ondoesnotmakea
dierence.
IJsareABSOLUTELYNOTimplementedexclusivelywithhashalgorithms.Sortmergealgorithmsandnestedloopalgorithmsareused
frequently.
RevMikeonOctober12,200711:10AM

Je:
Niceillustra on.Ithinkyourexamplewouldbeminutelymorereadableifthele handtablehadanIDcolumncontainingvalues
1,2,3,4(asitdoes)buttherighthandtablehadanIDcolumncontainingvaluesA,B,C,D(insteadofnumbers).Itjustmakesthat nybit
easiertotellwhat'swhatbyavoidingthetwosimilarlookingbutdierentcolumns.
MichaelChermsideonOctober12,200712:04PM

Verynicear cle.IuseaSqlitedatabase(viathecommandline)tomanagemydatabaseofcomicsandthiswouldhavebeenaverynice
thingtohavewhenIstartedout.Hopefully,thiswillhelppeopleingraspingSQLjoins.
JeFlowersonOctober12,200712:07PM

Iloveit.Thegrumpycomments,thatis.Plentyofcomplaintsthatthisistoosimplis caviewtobeuseful,naryalinktoanythingmea er.

AndSQLaddictswonderwhyeveryoneelsewhohastodealwithRDBSjumpsatthefirstlibrarythatletsthemtreatthemasanything
otherthanRDBS...
(justfinishedreworkingabigfatchunkofcodeoriginallywri entopullalltablesintomemoryanditera velyquery,join,andsortthem
evenanultrasimplis cunderstandingwouldhavedonesomeoneatonofgood)
Shog9onOctober12,200712:11PM

TheOracle(+),actuallypredatesOracle'susage,wasbecausewhenimplementedtherewasANSInoouterjoinsyntax.
InOracle8,theyaddedtheANSIsyntax.Nooneshouldbeusingtheoldsyntaxsohopfullyyouwillnotbeingseeingitoutinthewide.
willdieterichonOctober12,200712:17PM

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

12 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

Veryhelpful,thanks!!!
SpeedmasteronOctober12,20071:04PM

Whilethisisaninteres ngexampleofthesekindsofjoin,Ihavefoundthatinprac ceIalmostneverneedanythingotherthan

le /innerjoin.10yearsofprac ce,andit'snottheworldsmostcomplicatedsqlmostofthe me,butthereitis.ThankGODthatstupid
thingdidn'tillustrate"RIGHT"joinstoo!
Thefirst"le outerjoin"isthesameasjust"le join".Thesecondoneisthesameaswell,justput"b.idisnull"inthewhere.Andplease
don'tanyonetellmethattheop mizerisn'tgoingtofigurethatout(ordothesamethingwiththeouter.)Well,maybeitwon'tifitis
MySQL,butthatswhyyoudon'tusethat.
Die"outer",die!
AndrewonOctober12,20071:12PM

Excellentvisualrepresenta on.Thisshouldhelpthenoviceseeaconcretepictureintheirmindofthedierentjoins.WhileLINQisn't
quitesetbased(butclose)thesesamevisualiza onswouldworktheretoo.
ShawnWildermuthonOctober13,20073:40AM

Excellentar cle.
Thanksalot.
canyouwritesomethingsimilarforindexes.
mpunaskaratgmaildotcom
MandaronOctober13,20075:30AM

Somethingyoumayormaynotknow....
Duringthelate70'sMathswasrepackagedtoinclude
VennDiagrams
Matrices
andsoon,
withtheideaofpreparingforthecomingofcomputers.
AtleastinEnglandthathappened...

Whattheyteachnowishardformetodetermine,asmostvic msoftheeduca onalsystemcannotcountoutthecorrectchangeina

shop.
DavidGingeronOctober13,20077:08AM

Wowman,yourdesignisstellar.Contentisgreattoo...you'vegotanewreader.
WillHarrisonOctober13,20078:40AM

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

13 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

HolycrapthisisWAYoverdue!I'vereadTONSofSQLbooksandNONEofthemhadthissimplediagram!
TerrySmithonOctober13,20079:33AM

IthinktheCartesianequa onorCrossProductinVennDiagramformwouldbebothcirclescombiningtoformasphere.
TokesonOctober13,20079:35AM

Interes ng.Ifindjoinstobepre yobviouspersonally.Fundamentally,youhavetwosetsofrows,andyou'rematchingthemupbysome

criteria.Inaninnerjoin,youonlytakerowsthatmatchup.Inale outerjoin,youtakealltheonesfromthele ,plustheonesfromthe
rightthatmatch.Inarightouterjoin,theopposite.Ifyouhavemul plematches,youtakeallpossiblecombina onsofthem.
smackfuonOctober13,200710:00AM

Thankyou!I'vebeenusingSQLforsolongandhavemanybooksonSQLandIhaveneverseensuchsimpleexamples.Iloveit!
MitchellHashimotoonOctober13,200710:36AM

ThereasonmanypeoplewhodoDBdevelopmenthavesuchainadequateunderstandingofthissimpletopicisbecausetheSQL
languageisaninadequatetooltotrytoabsorbitfrom.So,ifyouneverweretaughtthisinschooloronthejob,it'skindofasurprise.
SomeDBMS'hassyntaxwithmoredescrip veverbs(e.g.,'Merge','Subset','Reject'),andVenndiagramsaswellinthevendor
documenta on.
SQL2005isanimprovementover2000.Butthesadfactisismanypeoplelearnaboutrela onalalgebrabywri ngSQLstatements.
Alesspre yversionofmuchofwhatJeissayingcanbefoundhere:
h p://db.grussell.org/sec on010.html
SteveonOctober13,200711:21AM

WithallrespecttoJeAtwood,bewareifyou'vereadthispostandthink,"wow,that'seasywhydidnooneexplainitlikethatbefore?"
Well,there'sareasonwhysomanySQLtutorialsandbooksdon'tusethesediagramsthey'reaninadequateexplana onofwhat
databasejoinsareallabout.
Joinsareindeedanalogoustosetopera ons,butitisonlyananalogy.Understandwhat'sreallygoingon,oryouwillgetburnedwhen
youactuallyhavetousethethings.
Ma onOctober13,200711:48AM

Verynicefirstpostonthesubject.IthinkmanyofthecommentsarevalidandIthinkitwouldbegoodtocon nuethisasaseriesthat
buildsupthemorecomplexissuesofSQLjoins.
I,wholearnedthisstuthehardway,reallyapprecieatetheseprimers!
RobCromaronOctober13,20071:18PM

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

14 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

IfonlysomeonehadexplainedSQLtomelikethiswhenIstarted...greatblog.
SteveonOctober13,20071:38PM

Greatstu,
thanks
VladonOctober14,20073:05AM

verycoolvisualisa on,pleasedomoreofthat
ravemanonOctober14,20077:44AM

theproblemwithvenndiagramsisthatardbmstableisnotthesamethingasaset.
WesleyTanakaonOctober14,20078:37AM

Greatvisualexplana on.I'veaddedthattomylistofreferencebookmarksforwhenI(o en)forgetthenatureofeachSQLjointype.

AndyonOctober15,20072:22AM

You'rerightJe.
Thisisausefulexplana on,asfarasge ngpeopletounderstandjoinsintermsofsomethingtheyalreadyunderstand(rudimentaryset
theory).However,itshouldbemadeclearthatthisisnotwhatismeantbythestatementthat"therela onaldatabasemodelisbased
onsettheory".Foralong me,IthoughtIunderstoodthatstatementbecauseIhadthisVenndiagramunderstandingofjoins.
Joinsdonotmapdirectlytothebasicaddi veopera onsofsettheory(union,intersect,dierence),whichcreatenewsetsbywayof
simplyincludingorexcludingelementsofothersets.TherearewaystogetthoseinSQL,butitinvolveschainingUNIONandMINUS
statements.
Joins,rather,aremappingopera ons,whichcreatenewsetsbytakinganelementfromeachset,accordingtoasetofrules,andlumping
themtogetherinanewelementforthenewsetthatisactuallyitselfasmallsetcontainingtheoriginaltwoelementsfromtheother
sets.
IfAisanaddi vesetopera on(representablebyaVenndiagram),thentheresultofAonsetsXandYisasetofmembersofXandY.
A(X,Y)={x1,y1,x2,x3,y4,y5,...}
Indatabaseterms,eachelementoftheresul ngsetissimplyarowfromeitherXorY.
Inrealworldterms,it'slikesor ngjellybeans.Throwthemallintoabucket,throughasieve.Thesieveeliminatesallbutthe
appropriateshapedones,butwhatgetsthroughiss llwhatyoustartedwith:anassortmentofjellybeans.
IfBisamappingsetopera on(representablebyalinedrawingmatchingques on),thentheresultofBonsetsXandYisasetofsets
eachcontainingamemberfromXandamemberofY.
B(X,Y)={{x1,y1},{x2,y2},{x3,y3},...}
Indatabaseterms,eachelementoftheresul ngsetisanewtypeofrowthatisalumpingtogetherofarowfromXandarowfromY.

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

15 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

Inrealworldterms,anexamplewouldbetakingabucketoforangesandabucketofapples,andpullingoutpairsofoneofeachthatare
thesamesize,pu ngthepairstogetherinli lebags,andthenpu ngthosebagsintoathirdbucket.Youdon'tgetabucketofapples
andoranges.Yougetabucketofpairsofanappleandanorange.
Lookingatitthisway,itshouldbereasonablyeasytoseethatthereisafundamentaldierencebetweenjoinsandthesimple
union/intersect/dierencesetopera ons.
WaterBreathonOctober15,20075:40AM

"Exceptthatvenndiagramsexplainsetlogic,andSQLJoinshaveveryli letodowithsetlogic.
Scaryhowmanypeopleareagreeingwiththis.
Wya onOctober12,200707:29AM"
Mythoughtsexactly!
cloud9ineonOctober15,20075:58AM

Je,thisisgreat!HowaboutvisuallyexplainingwhyyouhaveSELECTallthecolumnsyouareGROUPingby?Ialwayshaveahard me
explainingthatonetopeople!
JFonOctober15,20078:39AM

I'mafraidthisconceptismisleadingonthereasonsmen onedbySvenGroot.
TheresultofajoinisnotthoseitemsintableAandthoseitemsintableB,itisa"joined"combina onofthetwo.Thediagramsyou
havedrawnaresetdiagramsandarecreatedinSQLusingthesetoperatorsUNIONandINTERSECT.
JoinsareacompletelydierentconceptwhichIbelievearebestexplainedusingjustthetableoutputsyouhaveabovewithoutadding
theincorrectvenndiagrams.
JF,theexplana onbehindSELECTandGROUPBYcanbedescribedsimply.
SELECTCOUNT(*)FROMPeople
Thiswillreturnacountof100people
SELECTEyeColour,COUNT(*)FROMPeopleGROUPBYEyeColour
ThiswillreturnBlue20,Green50,Brown30
SELECTCOUNT(*)FROMPeopleGROUPBYEyeColour
Thiswillreturn20,50,30whilstthiscontainsthecountsthatyouarelookingfor,theyareuselessasthereisnorela ontoeachcount
andwhattherepresent.
RobinDayonOctober15,200710:53AM

AssumingPirateandNinjaareiden calrecordsinbothtables,theveryfirstthingthatcametomindaswhatresultIwouldwantwhenI
joinTablesABwas
Pirate
Monkey
Ninja
Spaghe
Rutabaga

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

16 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

DarthVader
Thisseemstobetheintui vemeaningofjoin.Venn'sdon'twork.
jamesmaidaonOctober15,200712:09PM

Thisisaverylikablear cle,Ipersonallyreallylikethewayyou'vedonethis...
DanielonOctober16,200711:01AM

Itneverceasestoamazemehowmanyfolksbecome"experts"onatechnicaltopiconcesomebodytakesthe metowriteaboutone.
Igivepropstotheauthorofthear clepresentedhere.Ineededtounderstandthisinforma on,anditwasputtomevisuallyinawayI
couldparsequiteeasily.
Asforthefolksdissec ngthisar cle,nitpickingeventhepremise,Iaskthatyoupleasewriteabe erone,andpostthelinkinsteadof
complaining.Iwillthendropbyandpickitapartoutofprofessionaldiscourtesy.
Thanks.
DadonOctober18,200711:48AM

TheCartesianproductof2setsisthesetofallcombina onsoforderedpairswhichcanbeproducedfromtheelementsofbothsets.So
theVennrepresenta onwouldlookliketwopilesofpokerchipsnexttoeachother,eachpilenumberingthenumberofdatapointsin
theotherbo omchip.
BarfoRamaonOctober19,20071:25PM

ThanksforthisgreatTutorial:)
LeononOctober22,200710:58AM

Nicewaytoexplainthingsinjoins.Byseeingtheexamplenobodycannotforgetjoins.
Keepitup.Hopeyouwillpublishmanymorear clewhichwillbeusefultoall.
Thanks,
Satheesh.
SatheeshonOctober27,200711:49AM

Ireallyhateyou.Ihavenorecollec onofyourapingmywifebutIs llhateyou.

CheesecakesLikeMeonOctober28,20079:45AM

thanksforthis,IaminaclassforcrystalreportsandIshowedmanyandithelpedthem
BrianonOctober30,20079:28AM

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

17 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

nicequicklessononjoins.Thisisexactlywhatiwanted.
JaxonOctober31,200711:25AM

Greatexplana onofthebasicsofinnerandouterjoins.
I'llbereferringito enandpassingiton.
Alsoverybravetoraiseanythinghereasnot100%perfectequalsrubbish
Mar n
Mar nonNovember2,20075:21AM

Verysimple,I'amalwaysconfusedupwithsqlopera ons,butIunderstandbe erwithsimplediagramslikeyours.

Thksagainforthisnicejob.
GhislainonNovember7,20073:36AM

ThankgodforVenndiagrams!Goodjobmate.
LukeonNovember13,20078:19AM

Wow!Terrifictutorial.I'veneverunderstoodJOINSbeyondtheconceptofthem.Thankyoufordemys fying!
Clay
ClayonNovember13,20078:52AM

Thankyousomuchformakingthisavailable.CheersfromtheUK
JamesPrinteronNovember14,20072:54AM

VERYGOODEXAMPLE,PLZSENDAFEWEXAMPLETOEMAILID.
myidissuresh_worldcom@yahoo.co.in
sureshonNovember15,20077:09AM

Onehelpfulsteptounderstandcomplexqueriestoindentthesubqueries.Thiscanbedoneautoma callywiththisfreeSQLForma er
ath p://www.sqlinform.com
GuidoMarcelonNovember22,200711:45AM

thisisawesome..
ArunaonDecember2,20073:00AM

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

18 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

Thisar cleisgreat!Apictureisworthathousandwords,andyourpictureshavehelpedmetounderstandSQLjoins.Thanks.
DerekonDecember7,200711:46AM

HeythatwasaCLEARexplana on...juststartedupwithsqlite
anddidntgetwhyiwasnotge ngtheselec oniwanted.
Nowido!
HennieonDecember8,20079:05AM

howdoyoujointwocolumnstogetherandgetonecolumninaquery.ForEX:ihavelastnameandfirstnamecolumnsinatable.iwant
tojointhesetwocolumnstogetherandgetlastnameandfirstnameinonecolumn??
InquisitoronDecember13,200712:15PM

Goodjob.
GurmeetonJanuary2,20089:45AM

AlthoughI'mfamiliarwiththetopic,Is lllikeyourniceandeasywayofexplainingthings,nicework.
A.NETDeveloper'sBlogonJanuary6,20089:35AM

ThiswasaGREATexplana on.IamworkingwithajuniorDBAandthishashelpedtremendously.Iwillhavetogobackandreadsomeof
yourotherblogs.
AWESOMEJOB!!!!!
SHALLonJanuary8,20088:58AM

Thereisaquerybuildertoolthatcomeswithoracleapex.Checkitout.
OracleTube.comonJanuary16,200810:44AM

Assomeonesuggestedtocreatedsomethingthemselvesandthencri cize;)I'vetriedtodescriberela onshipsamongvariousjointypes

usingERdiagrammhere:
h p://gplivna.blogspot.com/2008/01/sqljointypesimstudyingbitsql.html
Allcommentswelcome!
GintsPlivnaonJanuary25,20082:43AM

It'sagreatwaytoexplainthings....
thanks
PrajeeshonFebruary9,20085:05AM

31/08/2012 07:18 a.m.

Coding Horror: A Visual Explanation of SQL Joins

19 de 19

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-j...

ThanksforthisJe,wasstrugglingabitwiththeconceptbutyou'vemadeitclearenoughthatevenIcangetmyheadaroundit:)
AndyonFebruary18,20085:29AM

Awesome!
JulieMcBrienonMarch4,200810:09AM

IreallyhavetohandittoyouJe,thewayyoubreakthingsdownisjustgreat.Ireadtheblogsevery meyoupostone.Thisonein
par cularcameinhandyjusttheotherday.OurQAteamwashavingtroubleunderstandingwhatwasgoingoninthedatabaseandwe
developerswouldsendthemqueriestoruntofindthedatatheyneeded.TheQAteamhasarudimentaryunderstandingofSQL,but
couldn'tquitegraspjoins.Isentalinktothisar cletoourleadQA,andnowyourar cleisfirmlypostedonhercubewallforreference.
Justwantedtosaythankyoufortheblogs!
ArcondonMarch11,20088:56AM
Morecomments
Thecommentstothisentryareclosed.
Content2012JeAtwood.Logoimageusedwithpermissionoftheauthor.1993StevenC.McConnell.AllRightsReserved.

31/08/2012 07:18 a.m.