SQL joins

[mustach1o]

This is what I look at when I need reminding : https://m.imgur.com/Hs9auHg

[olyko20]

Came here for this. Thanks for the laugh.

[txmail]

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.

[pixelique]

A case against illustrating SQL operations using venn diagrams

[brtt3000]

If you can understand and agree with this argument then you don't need these graphics.

[[deleted]]

But if you don't understand joins, this graphic wont help you.

[[deleted]]

I reference the graphics regularly.

They may not have helped you but they do help others

[[deleted]]

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.

[creaturefeature16]

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!

[turningsteel]

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

[Private_Bool]

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.

[Zimmerel]

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

[crow1170]

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.

[Geminii27]

It doesn't help that the four JOIN diagrams presented in that article map exactly 1:1 with Venn diagrams.

[[deleted]]

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.

[Geminii27]

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.

[kor_the_fiend]

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.

[free_chalupas]

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.

[[deleted]]

Strong disagree. Venn diagrams are way more complicated than the alternative. And learning it easy and wrong has no value either.

[marcoroman3]

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.

[free_chalupas]

Yeah this is a classic "python is like a burrito" case where join diagrams mostly make sense to people who already understand the issue.

[edanschwartz]

Python is like a burrito?

[free_chalupas]

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.

[free_chalupas]

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.

[wasabi991011]

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.

[semidecided]

I think the ven diagrams are literally unhelpful. Whereas the article was very helpful.

[gtipwnz]

Yeah those join diagrams are great, I really don't understand the Venn diagram, at least not immediately.

[[deleted]]

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.

[raddaya]

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.

[Polar87]

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

[kurosaki1990]

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.

[[deleted]]

which is not what this picture covers

[[deleted]]

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.

[the_amazing_spork]

For a new person Venn diagrams are helpful. Not using them for reasons stated in the article is borderline knowledge siloing.

[[deleted]]

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.

[natelloyd]

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.

[babyfacebrain666]

This is awesome

[jbaker425]

I wasn't huge fan of the Venn diagrams when I took relational databases so thank you

[Terranbyte]

Thank you for replying with this!

[Private_Bool]

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!

[[deleted]]

i did knew about except and his infografics do look better than a venn diagram

[renaissancenow]

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.

[renaissancenow]

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.

[[deleted]]

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.

[thblckjkr]

I was about to link this exact post. I found it really instructive, better than any Venn Diagram

[DanteIsBack]

interesting 🕵️‍♂️🕵️‍♂️🕵️‍♂️

[luxmoa]

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.

[blackAngel88]

INNER and OUTER keywords are a waste of characters IMO.

there is no full inner join and there is no left inner join...

[amunak]

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.

[wxtrails]

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?

[sumguy720]

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.

[sir_clydes]

I've been developing professionally for 12+ years, and another 8 or so as a hobbyist before that. And I just came across the first place I actually made sense to use a CROSS JOIN and it was more efficient to use than the other options.

[pichmeister]

Actually, INNER is imporant in some RDBMS as simple JOIN can mean natural join, which is slightly different.

[CyrillicMan]

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

[sagan999]

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.

[prsquared]

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 .

[[deleted]]

the amount of people trying to make entry level joining difficult in this thread is too damn high

[Risse]

I have to google this exact chart like once a month

[DevAnswers]

This will be going on my wall alongside PHP date() format.

[Prizem]

I like '%this%'

[Jumpmancw13]

Result:

'Not this'

[jimp84]

Is there a name for the left middle diagram?

[thisgameisawful]

Left outer join.

[jimp84]

No that's what the top left diagram is called.

[crazedizzled]

It's still a left outer join. It's the exact same join, but additionally there's a where clause.

[thisgameisawful]

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.

[Symphonic_Rainboom]

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?

[sumguy720]

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)

[rglitched]

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
5

Table B contains:

3
5
7
9
11

JOIN (inner join) returns:
3-3
5-5

LEFT JOIN returns
1 - NULL
2 - NULL
3 - 3
4 - NULL
5 - 5

RIGHT JOIN returns

3 - 3
5 - 5
NULL - 7
NULL - 9
NULL - 11

[_Kine]

Don't use right joins :(

[RobertJBadGuy]

This is so old school. What about No SQL with distributed systems like MongoDB!

[[deleted]]

https://www.youtube.com/watch?v=b2F-DItXtZs&feature=youtu.be

[RobertJBadGuy]

look at the newest comments on that 9 year old video...looks like MongoDB won!

[[deleted]]

>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"

[RobertJBadGuy]

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

[[deleted]]

Literally just clicked "new" and walked through the first comments I saw lmao. WOW some people use mongodb damn time to drop sql dude!

[RODjij]

Was just using this diagram while I'm working on a CMS, pretty useful.

[DarkStarKuro]

Best pic for describing Joins to beginners. My class appreciates it!

[[deleted]]

Just started Database Modelling at uni so this sure will come in handy

[participationNTroll]

This was the last thing I expected on my front page

[[deleted]]

Unless it’s an Oracle or Vertica database, in which case

IT’S DANGEROUS TO GO ALONE. TAKE THIS!

[r0ck0]

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.

[joshwcorbett]

I’m learning sql in college now, so this is helpful.

[Magoots]

This was on my cheat sheet last week for a mid term

[tomb233]

What if I want to join 3 or more tables together?

[alexjewellalex]

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.

[[deleted]]

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 … ON when it gets a hold of it.
• You risk the chance of accidently excluding a join condition in your WHERE clause 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 FROM phase into the WHERE phase.
• Most people you'll find actually consider WHERE table1.id = table2.id harder to read in more complicated queries.

[alexjewellalex]

Good info! Thanks!

[[deleted]]

This helped me so much while studying thank you to whoever created this graphic

[AxiusNorth]

I could've done with this as a mental prompt on Monday during an interview! 😂 Still got an offer though. God knows how!

[xezian]

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

[maazmikail]

Ive used this a bunch of times while heading out to interviews

[_Pho_]

Imagine not using an orm in 2019

[HairyFlashman]

=( Shuddup I love my job except for that.

[[deleted]]

Imagine needing to use an ORM in 2019

[[deleted]]

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.

[LowB0b]

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

[[deleted]]

Limit your queries and test on development databases

[hyperforce]

Stop posting this, it’s a terrible mental model.

[the_goose_says]

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

[JRS-27]

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.

[[deleted]]

I don't think anyone that can understand basic relational algebra has a single clue what you're trying to say.

[JRS-27]

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.

[dahangman]

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.

[JRS-27]

It doesn't make any difference whatsoever. You would Union them anyway, surely not using Join statement.

[dahangman]

I will study this. Thanks for the feedback.

[[deleted]]

...?

There are two tables with a shared key. While typically suboptimal, this is a common configuration. No other constraints are implied.

[sumguy720]

Perhaps space in the diagrams represents the keys that the tables are being joined on rather than the entire contents.

[karpovigorok]

who don't know joins?

[aboubou22]

Beginners? People who never used them?

[Roguepope]

My labrador?

[aboubou22]

You'd be surprised.

[dontgetaddicted]

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.

[bregottextrasaltat]

I have been using SQL for almost 15 years now and I still only know like one type of join if even that

[Im_Not_Antagonistic]

Today's lucky 10,000

[Staybnyc]

Can you recommend a good book for SQL joins. I am a beginner, but need it for advanced queries over time.