“Getting” Joins 
I was asked to post this after explaining it to someone on IRC.
If you have tried to understand how joins work and constantly get confused about what join to use, you just need to keep a simple picture in mind ( I like pictures). I will be explaining joins by referencing a Venn diagram. Have no fear - I won’t get into any of the set theory/math involved. This is just a basic overview to give you an idea the data a particular join will return to you. This is not a technical discussion - just concepts.
We will start with just an empty diagram:
The T1 circle represents all the records in table 1. The T2 circle represents all the records in table 2. Notice how there is a bit of overlap of the 2 circles in the middle. Simple right?
I will use red to signify the records that will be returned by a particular join.
INNER JOIN
An inner join only returns those records that have “matches” in both tables. So for every record returned in T1 - you will also get the record linked by the foreign key in T2. In programming logic - think in terms of AND.
OUTER JOIN
An outer join is the inverse of the inner join. It only returns those records not in T1 and T2. “Give me the records that DON’T have a match.” In programming logic - think in terms of NOT AND.
LEFT JOIN
A left join returns all the records in the “left” table (T1) whether they have a match in the right table or not.
If, however, they do have a match in the right table - give me the “matching” data from the right table as well. If not - fill in the holes with null.
It should be noted that the same thing is possible with a right join - most people just use a left one.
LEFT OUTER JOIN
A left outer join combines the ideas behind a left join and an outer join. Basically - if you use a left outer join you will get the records in the left table that DO NOT have a match in the right table.
Again it is noted that the same thing is possible with a right outer join - most people just use a left one.
Theta JOIN
A theta join is the Cartesian product of the 2 tables and not normally what people are looking for - but what they sometimes get by mistake. How many of us have written a join similar to this only to get way more then we were ever expecting.
SELECT t1.*, t2.*
FROM table1 t1, table2 t2
WHERE t1.id = 5;
So there you have the basic concepts of joins. Next time you need to use a join and have no clue what to do to get the data you need from the database, draw a picture. It may help you figure out what join to use. Least it helps me.
Important:Please be sure to read the comments below. There are definite situations where my simplistic drawings are invalid and brought to light.
Disclaimer: I am by no means a database guru. Please be sure to learn more about joins from the many online articles available.
April 20th, 2007 at 1:29 pm
Ecellent explanation. Thanks!
April 20th, 2007 at 1:39 pm
Wow, great tip!
April 20th, 2007 at 2:08 pm
An image is worth 1000 words!
April 20th, 2007 at 3:28 pm
you’ve turned on the light in the attic!
April 20th, 2007 at 3:35 pm
Nice, but too bad a large part of it isn’t actually right.
Using Venn diagrams is misleading from the outset because it completely misrepresents concepts like inner joins and cross products, neither of which are reasonably communicated by the above layouts.
April 20th, 2007 at 3:51 pm
Nice article. I like visual explanations the best.
April 20th, 2007 at 4:07 pm
I do like circles ….
April 20th, 2007 at 4:15 pm
Diagram goodness. Mmmmmmmm.
April 20th, 2007 at 4:47 pm
A nice conceptual view for the ignorant. I would add that your outerjoin example should be the more specific FULL outer join.
The Theta Join, while uncommon, is sometimes exactly what you want. I use it more often than I ever thought I would during data warehouse ETL processing. It allows you to create a result with every possible combination of 2+ sets of data that don’t have a naturally joining field.
April 20th, 2007 at 4:49 pm
I am not sure what database you are using, but most that I know of like DB2, Oracle, and Postgresql implement LEFT OUTER JOIN as you describe LEFT JOIN. As a matter of fact, in many database engines the LEFT or RIGHT keywords imply an OUTER JOIN, and they have no such thing as just a LEFT JOIN, though some like DB2 have a LEFT EXCEPTION JOIN that works as you describe LEFT OUTER JOIN.
April 20th, 2007 at 5:42 pm
Thank You! Very very well done!!
April 20th, 2007 at 6:17 pm
Why haven’t I seen such nice Venn diagrams like this before. Thank you!
April 20th, 2007 at 6:18 pm
Blain,
That was exactly my contention with it. Ultimately a lot of people are going to be even MORE confused and misdirected about the various SQL join options. This certainly doesn’t help anyone “get” it.
April 20th, 2007 at 6:53 pm
hi -
I will admit ignorance to the specific kind of set terminology you are discussing (and also, so will Wikipedia which only contains descriptions of SQL’s join terminology), but you should probably qualify the terminology in this article as being in conflict with the terminology commonly used by structured query language (SQL). in SQL, “LEFT JOIN” and “LEFT OUTER JOIN” are synonymous, meeting your description of “left join”. there is no JOIN syntax directly supporting your particular description for “outer join” and “left outer join” - the “outer join” described here would be accomplished in SQL most directly by a FULL OUTER JOIN with additional criterion:
select * from t1 full outer join t2 on t2.t1id=t1.id where t1.id IS NULL or t2.id IS NULL
but since many databases dont even support FULL OUTER JOIN, something more like this is required:
SELECT t1.*, t2.* from t1 LEFT OUTER JOIN t2 on t2.t1id=t1.id WHERE t2.id IS NULL UNION ALL SELECT t1.*, t2.* from t2 LEFT OUTER JOIN t1 on t2.t1id=t1.id WHERE t1.id IS NULL
April 20th, 2007 at 7:27 pm
Great explanation!. Please allow me to translate this article into spanish.
April 20th, 2007 at 7:29 pm
Outer join diagrams and descriptions are fundamentally incorrect here. I am not sure Venn diagrams would correctly describe them in any case.
April 20th, 2007 at 7:32 pm
Almost every bit of this is plain wrong in so many ways. This is no way to ‘get’ joins.
April 20th, 2007 at 8:01 pm
I have been using SQL daily for the last five years and thought I understood joins pretty well. But your article showed me I’ve been using then wrong this whole time! I wonder how all my code still works properly in production? Oh well, better get cracking, I’m gonna be here all weekend fixing it!
April 20th, 2007 at 10:17 pm
This description of joins is (I guess) specific to MySQL, which I think is the polite version what the earlier commenters were saying.
I’ve worked with a number of databases, mostly Oracle, and this article explains for me why I can’t join tables in MySQL. The inner join does what I expect but where I want to outer join I can see from the pictures I want “left” instead. Thanks for this
April 20th, 2007 at 11:13 pm
what about for multiple joins?
April 21st, 2007 at 12:25 am
Yes - I work primarily with MySQL and SQL Server.
April 21st, 2007 at 1:21 pm
I’m not sure where you get your information on outer join but it’s incorrect as far as any SQL I’ve ever used.
April 21st, 2007 at 1:56 pm
This article is somewhat inaccurate, as others have pointed out, with regard to definitions of join operations. I, too, find that Venn diagrams usually confuse people more than they help to understand these concepts.
Join operations are defined in relational algebra. Don’t be scared off; the Wikipedia article is really fairly readable without extensive math knowledge:
http://en.wikipedia.org/wiki/Relational_algebra
Join operations in SQL (INNER, LEFT OUTER, RIGHT OUTER, and (FULL) OUTER) match the definitions in the article. The SQL INNER JOIN is a semi join. LEFT OUTER, RIGHT OUTER and (FULL) OUTER are the same.
April 21st, 2007 at 2:04 pm
louisw:
When dealing with multiple joins, it is best to think of it a nested joins of two data sets. I usually recommend that people start out by putting parenthesis in their JOIN clause, to help them picture that. For example:
select * from (T1 inner join T2 on T2.foo = T1.foo) inner join T3 on T3.bar = T1.bar and T3.baz = T2.baz
I hope this helps.
April 21st, 2007 at 3:17 pm
If you really want to get your head around this subject, the book to get is ‘SQL For Smarties’ by Joe Celko. (I’m unaffiliated, it’s just a top-notch book).
April 21st, 2007 at 3:33 pm
Good explanation! thanks!
April 21st, 2007 at 5:06 pm
Oh, for god’s sake, please stop publishing such crap without proper qualifications. This is so confusing. e.g., MySQL’s “left outer join” is totally different from what you’ve defined here. STOP WRITING STUPID UNRESEARCHED ARTICLES.
And the community should stop pulling such stuff to the top of delicious/popular. Gah.
April 21st, 2007 at 7:20 pm
MySQL is not a good reference for SQL at all. This is a general view of the results of joins. Every Database has a slightly different behavior with SQL sentences, and that why after a starting point like this, you have to research your particular database choice.
April 22nd, 2007 at 9:41 pm
This article contains quite a bit of WRONG information, when read in the context of the popular databases of today (MySQL, Oracle, MSSQL, Postgres, sqllite).
Inner Join: correct.
Outer join: WRONG. The article describes this as an XOR operation, when in fact an outer join is a superset of an inner join. An outer join will return all rows from T1 and T2, merged as much as possible, with null values filling the holes.
Left join: correct.
Left outer join: WRONG. There is no difference between ‘left join’ and ‘left outer join’. Additionally, a ‘left inner join’ is identical to a ‘inner join’
Theta join: correct. Also called cartesian or cross join.
April 23rd, 2007 at 3:43 am
That was an excellent explanation to the concept of joins by using visuals.
April 23rd, 2007 at 12:22 pm
Nice explanation dude but with a few glitches which have been explained by some other readers. Thanx for the post anyway.
April 23rd, 2007 at 12:55 pm
PERFECT!
nice..*shares*
April 24th, 2007 at 7:39 pm
I think that *some* of the diagrams may help some to understand joins better (regardless of how inacurate they may be).
Tip for everyone who is saying “No, this is completely wrong, you’re misleading people”:
EXPLAIN what is wrong, try yourself to explain accurately joins, and post a link here to your explanation.
Thanks!
April 24th, 2007 at 11:34 pm
This article is incorrect and misleading when applied to any SQL dialect that I have experience of (SQL Server, Oracle, InterBase/Firebird, Access (God Help Us!)).
I like the idea but your basic facts are wrong (as alluded to by other posters
April 25th, 2007 at 12:08 am
Sorry but - this is mostly VERY VERY WRONG - whatever you do don’t rely on this for either Computer Science theory of relational databases or for practical database SQL.
Did you actually check this with anyone?
For example (referring to C.J.Date or even Wikipedia) -
“The left outer join is written as R =X S where R and S are relations. The result of the left outer join is the set of all combinations of tuples in R and S that are equal on their common attribute names, in addition (loosely speaking) to tuples in R that have no matching tuples in S.”
That means a LEFT OUTER JOIN could be represented by the Venn diagram you use to describe LEFT JOIN.
The correct definition applies to both Relational Theory and to practical SQL standards.
April 26th, 2007 at 7:05 am
A left outer join is the same thing as a left join.
A right outer join is the same thing as a right join
An outer join is either a left join (or left outer join) or a right join (right outer join)
May 1st, 2007 at 2:26 pm
After the first person said: “Left outer join doesn’t work like that” didwe really need 50 other people to say the same damn thing??
I’m willing to bet that 90% of the people who left a negative comment to the author read the article, got all giddy and excited and wanted to write “Awesome! Thank you!” until they read a negative comment and then they were afraid to look like the idiots they are so they went with the “well, that’s wrong” comment instead…. f***ing lame-ass dweebs… grow a pair of balls next time and comment from your OWN THOUGHTS (if, indeed, you have any).
With that said… To the author:
AWESOME!! THANK YOU!!
May 4th, 2007 at 10:43 am
really excellent.
May 9th, 2007 at 3:29 am
This article is just outright wrong. Take the second example … an INNER JOIN is NOT, I repeat NOT the the same as the intersection of two sets as the diagram depicts. I’ve been working with relational databases for 25 years. Of course, if you don’t understand enough to know this is wrong, then you probably don’t care. If you did care you would go to a more authoritative source. If you believe it is right, then don’t bother trying to get a job as a SQL programmer.
May 16th, 2007 at 4:35 pm
Genial!
May 17th, 2007 at 9:30 am
The article is too confusing. Its better to understand joins by the help of examples then by venn diagrams
May 18th, 2007 at 10:29 pm
I agree with the people who have left a comment stating that in most popular databases (as well as in SQL-92 (ref below) ) , LEFT JOIN and LEFT OUTER JOIN are one and the same. Also, there is nothing called (just) OUTER JOIN .
However, I don’t understand the other set of guys complaining about how Venn Diagrams are no way of representing / explaining Joins. E.g. Mike (just above) says that INNER JOIN is not same as intersection of 2 sets . But none of these set of people have explained why ? To me its perfectly valid means of understanding Joins.
E.g. If Table A has primary key values { 1, 2, 3 } and Table B has {2, 3, 4 } then the Inner Join will exactly have the tuples corresponding to the primary keys of the intersection , i.e. A ∩ B = { 2, 3 } . What is wrong here ??
PS:
SQL-92 (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)
From Section 7.5 :
::=
INNER
| [ OUTER ]
| UNION
::=
LEFT
| RIGHT
| FULL
Notice, [OUTER] is optional
May 18th, 2007 at 10:33 pm
My apologies. The comment system ate up the angle-brackets as markup. Here is the relevant portion from SQL-92 standard again (hoping it works this time):
< join type > ::=
INNER
| < outer join type > [ OUTER ]
| UNION
< outer join type > ::=
LEFT
| RIGHT
| FULL
May 23rd, 2007 at 5:55 am
Excellent Matey. Never seen anyone explain it so simply and beatifully.
May 23rd, 2007 at 8:23 pm
Damnit, that’s a beautiful explanation ! Very well done .
I have struggled with this “join” concept for YEARS !
May 29th, 2007 at 10:11 am
Excellent one. Clear cut definition about Joins . Had great confusion of joins before this,Thanks much.
June 11th, 2007 at 3:53 pm
Actually, I suspect that many of the “wrong” comments are that a small piece is left out, bt is easily corrected.
That is using the WHERE clause. For example, on a typical LEFT JOIN, one usually includes either a “right_table.field IS NULL” or “IS NOT NULL.” What is described above as a LEFT OUTER JOIN is “LEFT JOIN `right_table` ON `right_table`.key = `left_table`.key WHERE right_table.field IS NULL”.
June 13th, 2007 at 6:45 am
I’m impressed - that’s extremely well documented and presented as far as tutorials go. Keep up the good work.
June 18th, 2007 at 1:54 pm
Actually much of the industry believes that a left outer join is the same as a left join. While I often have disputd this, oracle and other software developers believe the same. While I see your point and the naming scheme, I will often explictly point out in technical “A left outer join where the joining key is NULL” to make perfectly clear what I’m talking about.
July 2nd, 2007 at 2:51 pm
mm… I don’t want to criticize the author but I must express my concern. Others already pointed out, left (outer) join does not match Venn Diagram (the explanation is simply wrong). SQL syntax can express Venn Diagram, but it doesn’t mean Venn Diagram can express SQL syntax.
It’s not small mistake like someone said. I suggest the author to make simple table and write some code. Play with it few hours, and fix this article.
July 3rd, 2007 at 8:31 pm
Its a good explanation.
July 10th, 2007 at 8:37 pm
Great article to understand the concept of JOIN. I have been searching for something like that for a while to understand the ambiguity of JOIN and this fantastic. Like someone said, a picture worth a thousand words. What would make this article awesome is for some MySQL guru to add examples that go along with each picture. Any volunteers!!!
July 15th, 2007 at 10:12 pm
For god’s sake man, take this page down. Truly a case of the blind leading the blind!
July 23rd, 2007 at 4:54 pm
Great explanation. I really like the diagrams and I’m going to use this web site to better help one of my colleagues understand the joins concept.
July 24th, 2007 at 7:45 am
Its very useful and conceptually nice representation. but require more depth.
July 27th, 2007 at 5:51 pm
I hope my earlier comment gets through.
July 27th, 2007 at 5:54 pm
Oops, my longer comment did not get through. I’ll divide my comment into two.
Thanks for the article.
But I believe that the set diagrams above do not match the joins. For instance, the diagram “representing” INNER JOIN join does not represent an INNER JOIN query at all, but an INTERSECT query. Moreover, the query
select T1.*, T2.* from T1 inner join T2 on T1.field = T2.field
is not equivalent to the query
(select * from T1) intersect (select * from T2)
But I still believe that you can still represent joins by set diagrams. Here’s a representation of a basic join (called CROSS JOIN in SQL):
First, draw a circle representing a set we call T1 and put the letters A, B and C in it. That represents the set T1 = { A, B, C }.
Next, draw another circle and call it T2. Put the digits 1 and 2 in it. That’s now the representation of the set T2 = { 1, 2 }.
Now the operation T1 JOIN T2 produces a new set containing all possible pairings of each elements in T1 with each element in T2. That is, T1 JOIN T2 = { A:1, B:1, C:1, A:2, B:2, C:2 }. To represent JOIN, draw a circle and fill it with six smaller circles. Label the smaller circles A:1, B:1, C:1, A:2, B:2 and C:2.
That’s how to represent a basic join, but it’s a very limited representation by the way. In order to represent the other joins, we need to draw circles (representing sets) containing “points” (representing the elements). A “point” is something that we can represent as a pair of “coordinates” (x, y).
(To be continued…)
July 27th, 2007 at 6:02 pm
(Cotinuation…)
In order to represent the other joins, we need to draw circles (representing sets) containing “points” (representing the elements). A “point” is something that we can represent as a pair of “coordinates” (x, y).
Here’s how to represent the other joins:
Draw a circle representing a set T1. Fill T1 with three smaller circles. The smaller circles will represent the “points”. Label the smaller circle (A, 1), (B, 2), and (C, 3).
Draw another circle and call it T2. Fill T2 with three smaller circles representing the “points” (d, 1), (e, 2) and (g, 5).
We’ve just represented the following sets:
T1 = { (A, 1), (B, 2), (C, 3) }
T2 = { (d, 1), (e, 2), (g, 5) }
Remember that each “point” consists of “coordinates” (x, y).
(To be continued again…)
July 28th, 2007 at 12:41 pm
(…Continued, sorry for the long comment)
Here’s how to represent the other joins:
Draw a circle representing a set T1. Fill T1 with three smaller circles. The smaller circles will represent the “points”. Label the smaller circle (A, 1), (B, 2), and (C, 3).
Draw another circle and call it T2. Fill T2 with three smaller circles representing the “points” (d, 1), (e, 2) and (g, 5).
We’ve just represented the following sets:
T1 = { (A, 1), (B, 2), (C, 3) }
T2 = { (d, 1), (e, 2), (g, 5) }
Remember that each “point” consists of “coordinates” (x, y).
(To be continued yet again…)
August 2nd, 2007 at 2:43 pm
To. MOST All the above Technical SQL folks that say this is wrong:
Why don’t you instead write the SQL statements that exhibit the behavior of the Diagram’s depicted below. My guess is that you don’t know enough and just posted wrong. and Thanks to the few that actually attempted to “correct” the original poster with this in mind.
I believe these diagrams would be great if they actually started out with the SQL statement, then showed a venn diagram that represented the result set. (For those that can be visually represented in that way)
August 4th, 2007 at 3:58 pm
I’m sorry to join in the negativity, but you are misleading a lot of people with this set of pictures. They are wrong. You don’t understand SQL.
August 7th, 2007 at 8:24 pm
1. People think differently. Some people are good with logical concepts not tied to any visual diagrams. Others are more comfortable with diagrams. Both approaches are valid for many areas. The problem for the visual thinker is when there is an area which cannot be represented visually (such as multiple dimensions and abstract concepts in general.) However, most SQL queries are not so abstract and certainly the basic concepts can be helped along for some people by a visual example.
2. Yes many commonly refer to Left Joins as being equivilent to Left Outer Joins and the diagram for Left Outer Joins above is indeed incorrect.
3. Just do a Google Search of “Left Outer Join” and you will find plenty of correct examples. from authoritative sources. Some links:
http://www.databasejournal.com/features/oracle/print.php/3527921
http://www.oracledbaexpert.com/oracle/ocp/Joins.html
August 9th, 2007 at 8:42 pm
i join to everything said here. i enjoy joining so much.
August 9th, 2007 at 9:54 pm
this is really great. A way to learn the idea of joins easy going !
August 19th, 2007 at 1:56 pm
XARPB, you sir, are a *****wit. Rather than post \”you are wrong\” explain yourself, but I don\’t think you have the intellectual horsepower to do so. Go back to flipping burgers or some other trivial job that you obviously hold and leave the writers to write.
edit for language
August 20th, 2007 at 4:22 pm
Excellent,
What a nice and concise way to explain things,
Wonderful
the picture became clear to me in 1 min
August 20th, 2007 at 4:38 pm
Oh *&%$# this is crap
and that too really f’ing crap
dya think tht those who describe relational algebra are idiots ==))
u shd stop writing unresearched and STUPID Articles
if things became as simple as u say then those who proposed relational algebra will fr***in idiots
August 20th, 2007 at 9:14 pm
Well, I’m not an expert SQL programmer and I don’t know a “tuple” from a “scruple” and I have only used SQL with DB2, Oracle and Teradata so I probably don’t know what I’m talking about.
HOWEVER, I think the use of the Venn diagrams in your article were exactly the choice to clearly demonstrate what happens in SQL joins. The foul-mouthed a**holes that think they know everything and find great delight in slamming someone who takes the time and effort to attempt to help others understand concepts such as this not only need to have their mouths washed out with Lysol but should try backing up what they say with actual articles instead of just being bombastic.
I guess I don’t know what I’m talking about but I enjoyed the article. Thanks
August 21st, 2007 at 8:05 pm
you’re right in your disclaimer…you are not a database guru.
August 24th, 2007 at 8:21 pm
What can I say more than thanks to this really clear explanation. Your way was, without doubt the best way. Thanks.