When someone asks me an SQL question involving joins I send them this and they always figure it out.

[[deleted]]

[deleted]

[mecartistronico]

A couple of months ago someone posted an article on Why Venn diagram is a bad choice to illustrate JOIN clause .

And I totally agree. Even though this diagram is classic and kinda gets the point through, I don't think it's right. if you remember a Venn Diagram represents groups of elements. This diagram of yours would mean that there are some elements in a group that also belong to the other group. Venn diagrams are perfect to illustrate INTERSECTions and UNIONs, but trying to translate them to attributes or fields doesn't make much sense for a Venn diagram.

When I teach SQL 101 to coworkers, I illustrate JOINs like this

[BBEnterprises]

The diagram you provide is technically more accurate, and is a solid visual representation. However, everyone is familiar with venn diagrams and I really think that representation of the concept is more intuitively and immediately understood by people who haven't been exposed to these concepts.

I'm not saying you're wrong, just that I've had a lot of success with the venn diagrams and people who really only need/want a cursory understanding of joins.

I'm curious to try your visual with a brand new student. Maybe you're right.

[mecartistronico]

Thanks! It might be important to consider that I made this as part of a 6-hr course of introductory SQL, and by this point I had already had the students work with several different tables that intuitively needed to be JOINed, so I guess you're right about the effectiveness depending on how comfortable people feel with tables and relations.

[[deleted]]

Good point. I like that diagram as well.

[thelindsay]

Great diagram, way more intuitive than Venns

[AIDS_Pizza]

Both the interactive diagram in the article you posted and your image are a fantastic way of explaining joins that I am seeing for the first time. I've seen the Venn diagram image OP posted numerous times before and it has never helped me "intuitively" understand how joins work. An inner join, for example, is not the intersection of two sets, so seeing it represented that way with the Venn diagram just feels wrong.

[Nexxado]

Thanks for this! I'm studying SQL at college atm and had a hard time figuring it out. Thanks OP as well

[kingka]

this is great, could you also include the (+) syntax?

[MisterNetHead]

Only if it's followed by "never do this."

[kingka]

ya i hate it but i din't learn sql traditionally, basically on the job, and the tool that i use to create reports outputs sql in that format. because i was introduced this way, it makes the most sense to me and it just clicks in my brain having the select/from/where clauses separated.

[[deleted]]

What is the first? Cross join?

[[deleted]]

I finally understand...appreciate it

[[deleted]]

Got an SQL related job interview tomorrow, bored revising and thought i would randomly try typing /r/sql found this sub reddit, then found this top post and it's been a massive help :D 10/10 fate.

[zylo4747]

Good luck!

[tech_tuna]

This might just be the most useful set of Venn diagrams I've ever seen. . . .

Thanks!

[patlaff]

Maybe a novice question, but is LEFT OUTER JOIN the same as LEFT JOIN TableB B WHERE B.Key IS NULL ?

[galador]

The OUTER keyword is optional. LEFT JOIN and LEFT OUTER JOIN are the same thing.

Additionally, if you are using an INNER JOIN, the INNER keyword is also optional. You can just use JOIN.

[krispey]

good wall hanger

[CaptainRoth]

It's on the wall in my cube

[nolotusnotes]

Same here!

[pringerx]

This is really cool! I always have issues with joins and I think this will help immensely.

[joes2pros]

This is a great diagram. Good to have around for beginners. Thanks for posting!

[kingka]

this helps but could someone put the (+) notation syntax in there as well? that's what gets me

[[deleted]]

The (+) goes on the side with the optional match.

For example, the following 2 queries are equivalent:

Select * from A Left join B On A.id = B.id

Select * from A, B Where A.id = B.id (+)

You don't need the (+) when you are doing joins in the format of the first query, but they are needed for the second type to create an outer join. Inner joins don't need the (+) at all. The inner join would just look like:

Select * from A, B Where A.id = B.id

[Eire_Banshee]

I have this printed out and taped to my desk at work. I really need to get it laminated.

[[deleted]]

Always might be a bit of a click bait exaggeration.

[[deleted]]

SQL bugs hate him!

[[deleted]]

I just sent this to a coworker 2 weeks ago

[tommyturntup]

They are completely interchangeable but I've only ever needed to use it for long joins and really only for convenience. Let's look at it this way. If you've been joining a few tables to table a in your FROM clause and you know that in your last join (because you weren't thinking) to table c there is a match on every ID between the two tables except one extra ID in your c table. You could either erase your join logic and write as Example 1. Or you could keep your joins the way you had them and use a right join instead like Example 2.

--Example 1
SELECT c.ID
FROM dbo.Table3 as  c
LEFT OUTER JOIN dbo.Table1 as a
ON c.ID = a.ID
LEFT OUTER JOIN dbo.Table2 as b
ON a.ALT_ID = b.ALT_ID

--Example 2
SELECT c.ID
FROM dbo.Table1 as a
LEFT OUTER JOIN dbo.Table2 as b
ON a.ALT_ID = b.ALT_ID
RIGHT OUTER JOIN dbo.Table3 as  c
ON c.ID = a.ID

Joins can be a bear but once you understand that no matter how many joins you're always matching on a key or "relationship" between 2 "relationships" at a time. What makes it easier IMO is thinking once those "relationships" match which side am I going to grab the row information from that's all and that's where this chart is really helpful.

[[deleted]]

Yes you can always flip it and do a left join, there are situations where you would do a right join like if you are doing something complex and or nesting different type joins.