Visual Representation of SQL Joins


 
Undoubtedly the BEST article I've EVER READ on this topic. I give it to beginners all the time!!!!
Excellent paper that presents all joins' types.
Just a remark; there is an error in sections
Right Excluding JOIN
and
Left Excluding JOIN
. Conditions are inverted; WHERE A.key IS NULL for the left exc. join, and WHERE B.key IS NULL for the right exc. join.
You are mistaken about the conditions being reversed. Download the sample code and run the queries and you'll see that they are correct as described.
Excellent article, I didn't understand all these joins before reading this article!

modified 28-Feb-17 6:51am.


A.pk B.pk . Why cant you use some more descriptive names so that we actually know wtf you're talking about
Funniest comment ever. You're right, I guess it is too difficult to tell the difference between "A" and "B". Way, way too confusing. I totally should have named the tables "monkeyButt" and "usedKleenex". So here you go: monkeyButt.pk = usedKleenex.pk There so much more clear.
Just try this, to vote more than one time, because this article is great!!!!!!!
Referenced your __great__ article one time again in Q/A. Can unfortunately not vote more than 5. Thanks, Bruno

modified 22-Aug-15 11:01am.


Thanks for your great effort. It was much needed.
This looks good, but it essentially implements a full outer join. I guess we can get the other variants by starting two scans. One for the left side and one for the right side. Now, the question becomes on how you can do multiple joins. A N-way join could be implemented using the same trick. How would we do two joins (on different variables)? Say we now we also have an n-m relationship between projects and companies. We want to get all the people working for projects of a given company. How do we do this? Our additional documents would look like: { "type": "project of", "company_id": "IBM", "project_id": "Alpha" } { "type": "company", "_id": "IBM" } First thought would be to modify our map function to include our new relation and document type. The problem here would be that we have no way to do both joins at once. I.e. if we build an index on [company_id, project_id], we can not combine it with our [project_id, employee_id] in one go (obviously, results are not in the same order for both indexes and we do not have a single document with all company_id, project_id and employee_id). Should we use chained mapreduce here? Ignoring the documents and focusing on the relations, could we join on project_id to make [project_id, {company_id, employee_id}] pairs and then use chained mapreduce to re-order our index on [company_id, project_id, employee_id]? If we do that, should we use the reduce function to group our results (for a given project_id) before we copy, or is there another way?
Rumah Dijual
Challahhuakbar

Are you asking for help or giving input?
Thank you very much, it is very nice article.......
Hello Christopher I once wrote an answer on Stackoverflow where I used your image and a direct reference to this article. I never informed you of this and thought it was about time to see if I can contact you in one way or another.

The answer can be found here: http://stackoverflow.com/a/4715847/574115[^] and I hope I have provided enough credits/references to your article as I do not really want to infringe on your copyright.

If there is a problem or if I need to change anything on said answer then please let me know.
No worries, thanks for giving me credit.
The Venn diagrams were extremely helpful! Thanks for publishing!
Doug Girard Nirodha Software

www.douggirard.com


What kind of join is below? SELECT * FROM Table_A A, Table_B B WHERE A.Key = B.Key
That is an inner join. Run your query with the execution plan turned on and you will see that SQL converts this query to an inner join.
This is exactly what I was looking for, because I'm studying this right now. Perfect!
Very good simple comprehensive explanation.
Smile | :) Smile | :) Smile | :) Smile | :) Smile | :)
This is very misleading. The SQL is wrong in most cases, unless you specically want to have the same columns for each set of results. For instance, in practice the top 2 are "SELECT <select_list> FROM TableA A" & "SELECT <select_list> FROM TableB B", respectively. Some for the rest you could use NOT EXISTS (expect of course the case where it is just INNER and FULL OUTER JOIN). Which comes to the nub of the problem, this visual representation is for sets - SQL is based (however loosely) on Relations and not Sets (look for references to E F Codd or books by C J Date). It also at one point seems to imply that there is a "natural ordering" to the results from SQL. That is also very misleading, an ordering can appear to be there (without an ORDER BY), but one tiny change to the optimised execution plan can throw that out - almost seemingly at random. These are all bad things I've had to correct in developed code over many years. It must run to 1000's of statements that I have required change to correct ensuing bugs or perfomance problems.
was helpful Thumbs Up | :thumbsup:

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.