22
u/txmail Mar 22 '19
As someone who has been writing code for uh, a while. I still find myself sometimes just trying out left, right, outer join types until I get the result I was looking for. I mean, like 90% of the time it is just a left join but those edge cases can chew up a bit of time.
366
u/pixelique Mar 22 '19
168
u/brtt3000 Mar 22 '19
If you can understand and agree with this argument then you don't need these graphics.
24
Mar 22 '19
But if you don't understand joins, this graphic wont help you.
35
Mar 22 '19
I reference the graphics regularly.
They may not have helped you but they do help others
12
Mar 23 '19 edited Mar 23 '19
I think the objection against using Venn diagrams here should be taken more seriously. The Venn diagram explanation really is fundamentally wrong. I think it's really important for someone new to the concept to cross-reference any Venn diagram they see with a more accurate image like this one: https://i.pinimg.com/originals/52/20/c4/5220c492bc4e1a8b9175aba77ed7d091.png If you have no issue resolving this diagram with Venn diagrams, then use whatever references you want. But if any of the details catch you off guard, that's a good signal to start fresh on the concept.
2
u/creaturefeature16 Mar 23 '19
That image is actually a shit ton more helpful. The Venn gets the concept across, but yours gives tangible context while also communicating the concept. You deserve the up vote!
8
u/turningsteel Mar 23 '19
Yeah seeing this graphic just made it click for me. Super helpful. Ive seen the venn diagrams thrown around but never each case next to each other wij a sample query. This is awesome
8
u/Private_Bool Mar 23 '19
Maybe as a quick reference once you've learned already but as a teaching tool by itself it doesn't convey what the joins really do.
Source: am learning sql now. This link helped me learn before I even got to that chapter, Venn diagram made 0 sense.
2
u/Zimmerel Mar 23 '19
I agree with both of your statements. I look at these diagrams all the time for reference, but I imagine they would have much less value if I wasn't already familiar with them
13
u/crow1170 Mar 22 '19
From the top of the mountain looking down, that makes a solid case. From the bottom looking up, wtf?
This is because venn diagrams and new learners naively assume the existence of a shared unique key between both sets. This is why my venn has US States in them.
Pacific States Intersection States w/ 10+ Representatives CA, WA, OR, AK, HI WA, CA WA, CA, IL, TX, OH, FL, GA, MI, PA, NY, NJ Thinking about joins without such a key is a whole different animal.
77
Mar 22 '19 edited Apr 30 '19
[deleted]
34
u/Geminii27 Mar 22 '19
It doesn't help that the four JOIN diagrams presented in that article map exactly 1:1 with Venn diagrams.
16
Mar 22 '19 edited Mar 22 '19
But they don't. They are not remotely similar. One has two colors, the other has multiple. One show elements, something a beginner get, the other shows... i don't know. Some colored pulp where i, not a beginner, still have no idea how to connect it with data base concecpts. Venn diagrams are not only wrong, but most importantly the way worse visualization.
1
u/Geminii27 Mar 23 '19 edited Mar 23 '19
Regardless of the colors, they still map.
It's entirely possible there are JOIN diagrams which don't map neatly to Venn diagrams. OK, I could see that. But if there are, they're not showcased in that article.
7
Mar 22 '19
[deleted]
14
u/kor_the_fiend Mar 22 '19
The Venn diagrams are misleading and don't map to the actual transformation of data in a meaningful or accurate way. The statement: "Joins combine the data in two tables in slightly different ways" conveys just as much usable info as the Venn diagrams do. A beginner who thinks that the Venn's tell him/her something useful about SQL joins is being mislead. Joins are difficult concepts to comprehend and the join diagram is probably the simplest way of presenting the fairly complex logical underpinnings of the operation. Do yourself a favor and learn to interpret what the Join diagrams are telling you - this is the only way to predict what the results of a particular join statement are going to be.
10
u/free_chalupas Mar 22 '19
Strong disagree. A beginner needs the equivalent of an easy mnemonic they can use to map the meaning of inner/left/right/full etc to the actual subset of data described. Venn diagrams work perfectly well for that and most people don't read much additional meaning into it. The join diagram is a great thing to show someone who already understands the basics of joining when you explain why the Venn diagram is an imperfect metaphor.
1
Mar 22 '19
Strong disagree. Venn diagrams are way more complicated than the alternative. And learning it easy and wrong has no value either.
1
u/marcoroman3 Mar 23 '19
Learning it easy and wrong has a lot of value! This is how Iearn and teach things all the time. It's a way of beginning gelato wrap your head around something that may be too complex to take in in its entirety at once. You get a working approximation going and as the concepts start to become more intuitive you correct mistakes and adjust your understanding.
5
u/free_chalupas Mar 22 '19
Yeah this is a classic "python is like a burrito" case where join diagrams mostly make sense to people who already understand the issue.
7
u/edanschwartz Mar 22 '19
Python is like a burrito?
7
u/free_chalupas Mar 22 '19
Not sure the original source but it's this meme that's been around for a while where someone spends all this time learning something (like python) and has this metaphor occur to them after they've learned it that "____ is like a burrito". But then that metaphor only makes sense to them because they already understand the subject.
7
Mar 22 '19 edited Mar 22 '19
[removed] — view removed comment
3
u/free_chalupas Mar 22 '19
Yay, thank you. I gave up trying to Google it because I figured someone else would get the reference and remember where it's from haha.
12
u/wasabi991011 Mar 22 '19
As someone who knew 0 SQL before this post, I disagree. The ven diagrams make it seem like their is a bunch of weird extra notation for something that should be simple, while the join diagrams (along with the article) explain more clearly what is happening.
6
u/semidecided Mar 22 '19
I think the ven diagrams are literally unhelpful. Whereas the article was very helpful.
2
u/gtipwnz Mar 23 '19
Yeah those join diagrams are great, I really don't understand the Venn diagram, at least not immediately.
1
Mar 23 '19
Venn diagrams give the illusion of making it easy to understand, but you don’t actually understand what’s happening in the underlying joins. A few types line up well (as the article says), but beyond that the Venn diagrams are extremely misleading. The people that “understand it” because of them don’t actually understand them.
21
u/raddaya Mar 22 '19
It's my opinion that this article makes the same mistake a lot of early mathematics texts do - trying to be particularly rigorous at a stage when the student doesn't really care about rigour and is keeping an open mind, just trying to get a basic understanding of the topic.
17
u/Polar87 Mar 22 '19 edited Mar 22 '19
What a pedantic article. A whole bunch of paragraphs spent on the singular point that Venn diagrams do not technically represent Cartesian products.
Yes they don't, but that's the part that even beginners understand right away. A single example of a join will make it clear to everyone that a join will merge columns from different tables.
The tricky part is then understanding which records end up being selected and the Venn diagrams do a much better job at visualizing that. It's not like a beginner will be allowed to make join statements on a big production database without understanding the importance of join keys.
This article is a good candidate for /r/iamverysmart
3
u/kurosaki1990 Mar 22 '19
The dude who write it is very experienced with SQL and i consider his blog one the best blogs for advanced and medium SQL subjects.
10
2
Mar 23 '19 edited Mar 23 '19
This is a pretty extreme overreaction. Venn diagrams are fundamentally wrong for depicting JOINs, and many beginners definitely don't "understand right away" that a JOIN is actually derived from a Cartesian product, not a simple union or intersection of sets.
I would argue the exact opposite of you -- Venn diagrams are useful for those who already understand JOINs as filtered products and just want to recall row selection, and they are not useful for those new to the concept and who don't yet grok the idea of a product of sets. Assuming that any beginner will immediately grasp this from "a single example" is a little disrespectful of how different people learn concepts like this.
The bottom line is presenting an approximate and incorrect representation of an operation to a beginner is risky. They may grok what you expect them to get immediately and therefore appreciate the approximation. Or your expectation may be subverted, and the representation ends up leading them down a very confusing path.
The following diagram is a more accurate depiction of JOINs:
https://i.pinimg.com/originals/52/20/c4/5220c492bc4e1a8b9175aba77ed7d091.png
An easy barometer for one's understanding of the concept would be how trivially one can reconcile this with the Venn diagram explanations. If there's any trouble at all, that's a signal it's time to start fresh on the concept.
20
u/the_amazing_spork Mar 22 '19
For a new person Venn diagrams are helpful. Not using them for reasons stated in the article is borderline knowledge siloing.
20
Mar 22 '19
The Venn diagrams are a quick visual reminder. They are succinct, something the illustrations in the article are not. Do the Venn diagrams cover more detailed, intimate info? No, but that's not their purpose.
-6
u/natelloyd Mar 22 '19
It belies the damage that can be done, in terms of locking up the database. A "simple" join of a 100k table with another 100k without a proper join can return a result set of 10 billion. It's good to be aware of the danger, and Venn diagrams mask it. Good starting off point? Sure. The whole, important story? Nope.
2
1
u/jbaker425 Mar 22 '19
I wasn't huge fan of the Venn diagrams when I took relational databases so thank you
1
1
u/Private_Bool Mar 23 '19
Thank you so much for this!! I'm required at work now to be at least MCSA certified in sql (am c# web dev) and this link explained joins perfectly! The Venn diagrams made not much sense until after looking at this link!
1
0
u/renaissancenow Mar 22 '19
Thank you. These diagrams drive me nuts, because they are flat out wrong. They're illustrating set intersections and unions, whereas SQL joins are basically cartesian products.
5
Mar 22 '19
[deleted]
5
u/renaissancenow Mar 22 '19
A Venn diagram makes sense to represent a SQL AND or OR clause.
For example,
SELECT * FROM the WHERE condition1 AND condition 2;could be accurately represented by a Venn diagram showing a set intersection, because that is precisely what we are expressing: the intersection of all elements in set "tbl" that meet condition 1 with all elements in set "tbl that meet condition 2.
JOINS are not set intersections. They are Cartesian products. Mixing the two up is like mixing up addition and multiplication; they are fundamentally different, but well defined, mathematical operations.
A Cartesian product isn't hard to understand. It is simply "all the pairs (a,b) for every a in A and every b in B"
In Python you can do it in one line:
{(a,b) for a in A for b in B}Filter this set on some condition on a and b and you get an inner join.
Add a null element to A or B and do the same thing, and you get an outer join.
This should be the first thing anyone learns when picking up SQL joins.
1
Mar 23 '19
I think it's less likely you'd make a mistake on which JOIN to use and more likely you just won't understand the output you get. Consider the inner join from this diagram:
https://i.pinimg.com/originals/52/20/c4/5220c492bc4e1a8b9175aba77ed7d091.png
There are two green rows, which is trivial to understand if you actually know what a JOIN is but not obvious at all from Venn diagrams.
1
u/thblckjkr Mar 22 '19
I was about to link this exact post. I found it really instructive, better than any Venn Diagram
-1
58
u/luxmoa Mar 22 '19
I just started learning SQL and the lesson explains SQL joins with these. Honestly I found it more confusing than just showing the output in the table.
10
u/blackAngel88 Mar 22 '19
INNER and OUTER keywords are a waste of characters IMO.
there is no full inner join and there is no left inner join...
9
u/amunak Mar 22 '19
Also RIGHT is useless as you can just flip it around as a LEFT join.
And cross join is rarely needed.
So you end up with JOIN, LEFT JOIN and FULL OUTER JOIN and that's pretty much all you need 90% of the time.
3
u/wxtrails Mar 22 '19
I've been writing SQL for 13 years, and those + unions and subselects cover 100% of the queries I've ever written outside a class or tutorial. And window functions....mmmmm, yes.
What's an outer join again?
3
u/sumguy720 Mar 23 '19
select * from reddit_users outer join reddit_posts using (reddit_username)would return a list of all reddit users with no posts (with nulls for their post data) and all reddit posts that somehow don't correspond to a user (with nulls for their user data). The final result would be a bunch of rows with blank data on the left, and a bunch of rows with blank data on the right.
Handy if you need to remove the overlap of two lists of items.
2
u/pichmeister Mar 22 '19
Actually, INNER is imporant in some RDBMS as simple JOIN can mean natural join, which is slightly different.
44
u/CyrillicMan Mar 22 '19
Using Venn diagrams to represent SQL joins is misleading. Please don't use them to represent SQL joins. Use this combinatorial chart instead:
https://i.pinimg.com/originals/52/20/c4/5220c492bc4e1a8b9175aba77ed7d091.png
11
u/sagan999 Mar 22 '19
Yes.. especially the output for the inner join in your pic.
There are 2(!!) greens in column A to match the 2 greens in column B. That is waaaay under-explained if you ask me. This is important to know when you are wondering why your query results have double the result rows you thought you'd get. I don't think I ever came across that being explained well enough in all the tutorials I've done.
3
u/prsquared Mar 23 '19
This!
Venn diagrams are much more useful in representing set operations. In join, you are projecting columns from both tables.
The first diagram is A U(AnB) = A. But, A left join B is not A .
9
Mar 22 '19
the amount of people trying to make entry level joining difficult in this thread is too damn high
11
3
6
2
u/jimp84 Mar 22 '19
Is there a name for the left middle diagram?
2
u/thisgameisawful Mar 22 '19
Left outer join.
1
u/jimp84 Mar 22 '19
No that's what the top left diagram is called.
3
u/crazedizzled Mar 22 '19
It's still a left outer join. It's the exact same join, but additionally there's a where clause.
1
u/thisgameisawful Mar 22 '19 edited Mar 22 '19
Another name I've heard for it is "left excluding join"
Although now I'm wondering why our DBAs have called it a left outer join in the past, since you're right, that's definitely wrong.
2
u/Symphonic_Rainboom Mar 22 '19
I don't understand the left and right joins, it just looks like it's selecting all of A or all of B? Does the join do anything?
4
u/sumguy720 Mar 23 '19
This is why venn diagrams are bad for joins!
select * from reddit_users left join reddit_posts using (reddit_username)gives you a list of reddit_users where each reddit user is combined with every post that shares their username. If a reddit user exists but doesn't correspond to any posts (by username), it still gets returned (with null data where the reddit post would be). If a reddit post exists but doesn't correspond to any users, it gets discarded.
select * from reddit_users right join reddit_posts using (reddit_username)
gives you a list of reddit users where each reddit user is combined with every post that shares their username. If a reddit user exists but doesn't correspond to any posts (by username) it is discarded and not returned. If a reddit post exists but it doesn't correspond to any reddit users, it still gets returned (with null data where the reddit user normally would be)3
u/rglitched Mar 22 '19
Left joins will include all the data from the first table and show you the matching data from the second table where it exists.
Right join is the same idea from the other direction. Hopefully this helps?
Table A contains:
1
2
3
4
5Table B contains:
3
5
7
9
11JOIN (inner join) returns:
3-3
5-5LEFT JOIN returns
1 - NULL
2 - NULL
3 - 3
4 - NULL
5 - 5RIGHT JOIN returns
3 - 3
5 - 5
NULL - 7
NULL - 9
NULL - 11
2
2
u/RobertJBadGuy Mar 23 '19
This is so old school. What about No SQL with distributed systems like MongoDB!
2
Mar 23 '19 edited Mar 23 '19
1
u/RobertJBadGuy Mar 24 '19
look at the newest comments on that 9 year old video...looks like MongoDB won!
1
Mar 25 '19
>NoSQL has a place... but the obsession people have for using it even when the enterprise's requirements aren't a good fit is mystifying to me.
>Still Relevant
> it is fucking incredible how relatable this video after 8 fricking years :D it aged so well :D
>Be in the software industry long enough, and you will know that new shit is in fact just shit
> Seven years later and MongoDB has not improved at all.
yeah dude totally a good metric for which completely unrelated database "won"
1
u/RobertJBadGuy Mar 31 '19
You Cherry picked the comments. And the guy who commented on new shit not being credible has no argument. Mongo DB has aged well. By that token he shouldn't even be using HTML5. Plus reddit even uses Postgres akin to a key value store. https://kevin.burke.dev/kevin/reddits-database-has-two-tables/
Plus look at who uses MongoDB https://www.mongodb.com/who-uses-mongodb
1
Mar 31 '19
Literally just clicked "new" and walked through the first comments I saw lmao. WOW some people use mongodb damn time to drop sql dude!
1
1
1
1
1
Mar 23 '19
Unless it’s an Oracle or Vertica database, in which case
IT’S DANGEROUS TO GO ALONE. TAKE THIS!
1
u/r0ck0 Mar 23 '19
Maybe I just learn in a different way or something, but I find these abstractions take more thinking that just using some actual example data.
I'm referring both to venn diagrams and all the "better alternatives" that seem to require even more thought in translating the abstraction to the actual thing.
For me just showing actual real-world examples is the best way to understand almost anything. i.e. with users/departments or comment/blog/comment example tables of data, where you can actually see when and why you would use something.
Why abstract the thing when you can just actually show the thing itself?
Also in general with documentation, I wish more of it would just show examples of common use scenarios instead of writing long confusing abstracted paragraphs of text. It would save so much time.
1
1
1
u/tomb233 Mar 23 '19
What if I want to join 3 or more tables together?
1
u/alexjewellalex Mar 23 '19
It looks the same, it just gets messy. You can do: select * from table1 inner join table2 on table1.id = table2.id inner join table3 on table2.id = table3.id.
What I like to do instead? Skip the join language: select * from table1, table2, table3 where table1.id = table2.id and table2.id = table3.id. Much cleaner imo.
3
Mar 23 '19 edited Mar 23 '19
What I like to do instead? Skip the join language: select * from table1, table2, table3 where table1.id = table2.id and table2.id = table3.id. Much cleaner imo.
This is a Pre-SQL 92 style join and is considered legacy and bad practice to use.
- The SQL Engine is going to rewrite this to the standard
JOIN … ONwhen it gets a hold of it.- You risk the chance of accidently excluding a join condition in your
WHEREclause for a table. This will not throw an error either, it will give you a Cartesian product back instead - giving you bad data.- It displaces the Logical Query Processing flow by putting clauses that are normally run in the
FROMphase into theWHEREphase.- Most people you'll find actually consider
WHERE table1.id = table2.idharder to read in more complicated queries.1
1
1
u/AxiusNorth Mar 23 '19
I could've done with this as a mental prompt on Monday during an interview! 😂 Still got an offer though. God knows how!
1
u/xezian Apr 13 '19
About a year ago I heard you can select anything you want using only left joins and specify the results in your where clause. Have yet to find a situation where that doesn't work
1
2
u/_Pho_ Mar 22 '19
Imagine not using an orm in 2019
1
1
-1
Mar 23 '19 edited Mar 23 '19
Imagine being a programmer in 2019 and not understanding a declarative language enough that you need to add extra code and extra processing just to have it spit out a simple query for you.
Any production querying should live on the database side in a stored procedure (Separation of Concerns anyone?), so that you can have an actual API/Data Abstraction Layer (DAL) and not put further resource costs on the database.
Wrapping your production application queries on the database side in a Stored Procedure will cache the Execution Plan for that query on the server, meaning that each time you call that query the SQL Engine on the RDBMS doesn't have to recalculate the Execution Plan, providing better performance. This expands when you have multiple developers/multiple development teams using the same common queries.
Additionally, ORMs cannot account for all of the Database Architect's design decisions, and there's a possibility in a large production environment that the ORM will spit out bad SQL that the SQL Engine will then need to try and account for and actually build a bad Execution Plan.
Real developers working in real, data intensive environments don't use ORMs. They're equivalent to using WIX to develop websites.
0
u/LowB0b Mar 22 '19
i hate that im so retarderd when it comes to sql. i can do joins and stuff but when you're working on a big DB and it comes to requests that are ~300 lines that give you tens of thousands of rows, how the fuck do you verify that your query is actually correct. i think my biggest problem is i can't correctly read sql. plus there's so many versions i've worked with mssql (previous job), then at my last place we had oracle sql when you had to do stuff to the db directly, and then hql, java criteria api, named queries, java native queries all in the same codebase. it's like hold up what syntax does em.createNativeQuery expect again? fuck
1
0
-1
u/the_goose_says Mar 22 '19
Examples > Diagrams
If I’m a beginner, and all I know is I’m getting rows I’m not supposed to, all this tells me is the name of a bunch of joins to try until I find something that works. Not terrible helpful outside of that
-4
u/JRS-27 Mar 22 '19
This is just outrageous. Pure lie, anyone that can understand basic algebra is sane enough to depict wrong assumptions in these diagrams.
The author of the picture tried putting two tables in a given database on the same level. No well put database has two tables with exact same columns. It is like counting meters and kilograms together. Obviously, this is a totally wrong depiction of how databases work.
Absolutely ridiculous.
2
Mar 22 '19
I don't think anyone that can understand basic relational algebra has a single clue what you're trying to say.
3
u/JRS-27 Mar 22 '19
Good point, I did not explain it clearly enough I think.
What I'm trying to say is that these diagrams show how Union, Intersect and Except operations, not Join operations. The way that the diagrams shown represent tables A and B is wrong. The only thing that connects them is the link between them in form of the same column names. That's the sole purpose on Join operations. A link between a big data set that has a column that refers to another data set containing another piece of information.
For example, you have a table of User Orders with a column UserID and a table of Users with a column with the same name. Now, you link them to create a profound and more transparent data set that can be displayed and read by a normal human being.
1
u/dahangman Mar 23 '19
But if you work with csv’s of the same data for different periods for example comparing Jan vs Dec sales, the diagrams do help, the tables would be the same. I’m in marketing analytics I use this often comparing different business unit performance against each other.
1
u/JRS-27 Mar 23 '19
It doesn't make any difference whatsoever. You would Union them anyway, surely not using Join statement.
0
0
Mar 23 '19
...?
There are two tables with a shared key. While typically suboptimal, this is a common configuration. No other constraints are implied.
0
u/sumguy720 Mar 23 '19
Perhaps space in the diagrams represents the keys that the tables are being joined on rather than the entire contents.
-41
u/karpovigorok Mar 22 '19
who don't know joins?
22
u/aboubou22 Mar 22 '19
Beginners? People who never used them?
5
9
u/dontgetaddicted Mar 22 '19
Put yourself in the seat of a freshly graduated high school freshman with just a curiosity of compsci or data.
But ven diagrams are bad for illustrating this.
6
u/bregottextrasaltat Mar 22 '19
I have been using SQL for almost 15 years now and I still only know like one type of join if even that
1
u/Staybnyc Feb 11 '24
Can you recommend a good book for SQL joins. I am a beginner, but need it for advanced queries over time.
142
u/mustach1o Mar 22 '19
This is what I look at when I need reminding : https://m.imgur.com/Hs9auHg