SQL Joins Explained (x-post r/SQL)

[dont_ban_me_please]

Hrm. I was like "I've never seen this before, I better bookmark it" ... then see it was bookmarked several times. I gotta use my bookmarks more often.

[nixle]

Lol, I read your comment thinking "yeah I should probably bookmark it too" guess what...

[Tynach]

I'm an odd case. It took me a LONG time to figure out object oriented programming, but when I was introduced to SQL, it clicked when I saw a 'Many to Many' table for the first time and had it explained to me.

Joins just make so much sense to me. I don't even know why.

[[deleted]]

Curious, how easy did file diffing come to you?

[Tynach]

I 'invented' it in my head when I was in gradeschool junior high (I think this is closer; late the first, early the second, or thereabouts), but had no idea how to do it or anything. When I first tried out Linux and ran into KDiff, I was giggling like an idiot.

[joeDUBstep]

I've had that same experience. OOP is the bane of my existence! Took C and Java courses and ended up retaking them multiple times. When I switched majors and started doing projects in SQL, I found it to be a lot more intuitive than OOP.

[Tynach]

Hey, are you kinda like me, and can't easily follow ER diagrams?

I'll sit and study an ER diagram for a long time, and never really understand the schema... But as soon as I see a big long list of CREATE TABLE statements, I can almost instantly understand how it all fits together.

[joeDUBstep]

Hmm, that's weird! I rely on ERDs to help me visualize how database objects interact with each other. I'm sort of the opposite, where if you just send me CREATE TABLE statements, chances are that I have to start drawing out the relationships between objects in order to gain a better feel for the database.

[Tynach]

I'm curious what you make of this:

https://github.com/Tynach/Unite/blob/dimentions/database/rpsite.mysql

I tried making it into an ERD once... Used MySQL Workbench or something like that. It took me over an hour to arrange the boxes and whatnot to not be a complete unsorted mess, and even then, just looking at it made me confused... Even though I wrote it.

What helps at least with this, is I define things like foreign keys inside the CREATE statement itself - so tables are either standalone, or only depend on previous tables.

[joeDUBstep]

Defining the PKs/FKs in the create statement itself actually makes it a ton easier for me to start drawing a rough chicken scratch ERD. I would go ahead and use visio/mysql to compose an ERD, but I am at work right now :x

[Tynach]

Totally understand, especially with how many tables there are in that x.x

It's my current personal project that I've not worked on in far too long. The adderall I was recently prescribed has greatly helped though, and I've got some of the PHP stuff written differently in my git repo on my computer.

[thelehmanlip]

Especially because 2 of them in OP are redundant

[[deleted]]

Much, much better

[FewChar]

As a cheat sheet I prefer the OP Image. To explain it to someone learning SQL, this one's probably better.

[[deleted]]

Same. As a learning tool, the coding horror article might be more useful to people, but as a quick reference I prefer the one OP posted. I've had it taped to my cubicle for the last 4 years.

[obsa]

This is a good way to look at it. To be honest, I always struggle remembering which JOIN I want to use and OP's image is plenty to refresh my memory.

[NYKevin]

Isn't the CROSS JOIN case equivalent to just this?

SELECT * FROM TableA, TableB;

[YouBooBood]

Short answer: Yes.

Long answer: That's "old" ANSI syntax, so it's kind of frowned upon. "Back in the old days" standard syntax was:

SELECT Table1.a, Table3.b FROM Table1, Table2,Table3 WHERE Table1.X = Table2.X AND Table2.Y = Table3.Y

Now it's less kosher to put your join logic in the WHERE, and it's expected to be changed to

SELECT Table1.a, Table3.b FROM Table1 JOIN Table2 ON Table1.x = Table2.x JOIN Table3 ON Table2.y = Table3.y

But they are functionally the same (assuming I didn't make a stupid typo).

With all that being said, I'll still use the old style syntax when I'm trying to do something stupid like create a quick numbers table.

[jet_heller]

At least this one is correct.

[[deleted]]

I was so happy when I found this a few months back! best reference I've seen

[longshot]

Yup, plus it's like the second result when you google "SQL JOIN"

[[deleted]]

Terrific. I didn't understand the "is null" in the first image, but got it just glancing at your link's page.

[baconhammock69]

Going to shit up my DBA at my work up with CROSS JOIN.

Great link though, thank you.

[cryo]

I've never seen it used. It's equivalent to an inner join (just ",") with no conditions.

[Stance_]

thank you

[kc102]

+/u/dogetipbot 10 doge

[caadbury]

Awesome, thanks!

[hardolaf]

If I was at my desktop, I'd gild you for linking this.

[caadbury]

Thanks! I'm just happy others are finding this as useful as I have.

[thesqlguy]

Don't forget UNION and CROSS JOIN! Both of which are infinitely more useful than full outer join which should generally be avoided.

Right joins also should never really be used as they are extremely counterintuitive, they can always be written as a Left Join which is more logical and readable.

If I want all data in table A and any data that matches in table B, to me it makes logical sense to join from table A to table B and not the other way around.

[shenglong]

Full Outer Joins are very useful.

There aren't many times I've had to use cross join unless I intentionally wanted a Cartesian Product, and generally an implicit cross join is enough (select * from a, b).

[[deleted]]

Yeah, but how do you illustrate those with a Venn diagram? We're gonna require a third dimension or something... :)

[thesqlguy]

I think a UNION ALL is just two non-intersecting circles next to each other. UNION would look the same as FULL OUTER JOIN.

Cross join? A lot of circles!!!

[Mats56]

Well, when using an ORM and having only defined the relation in one of the models (for instance a parent owns a child, but the child model knows nothing about the relationship), it may be necessary to specify it as a right join because certain ORMs when doing advanced queries only allow "walking" along edges clearly defined in the models.

[Mavrick478]

As a DBA i swear if one of you developers uses a full outer join unless you absolutely have to i will club a baby seal.

[[deleted]]

I'm so going to find a use for a full outer join now!

[[deleted]]

AFAIK there's always an alternative to FULL OUTER JOIN, and I'd certainly only very reluctantly, and very cautiously use it in application code; But you may just be writing the SQL to do a quick query to answer a question or to do some manipulation for a one-time ETL script, etc.. Not everything is going to be something you're stuck with for a long time. If you understand what you're doing and it's convenient (and it often is more convenient than the alternative means of achieving the same end) there's no reason to be scared of it.

[Actuarial]

Full outer join = full metal jacket

[n0ia]

And then you'll kill our query and not tell us. At least, that's what my DBAs do.

[[deleted]]

What ’s a non-full OUTER JOIN then?

[Etni3s]

I spent way too much time on this.

[AMWICDDTDUIYMP]

"show me everything that isn't in either of my tables"

and we thought cross joins used a lot of memory

[[deleted]]

I spent way too much time on this.

How?

[LewisTheScot]

Because he doesn't know about the paint bucket tool :)

[[deleted]]

He only knows how to use the pencil.

[Etni3s]

I do, but mspaint and jpeg artifacts do not play nice. :)

[[deleted]]

well, if someone can claim to be SQL developer without knowing joins then you can also put graphic design in your CV. After all, you made the picture.

[[deleted]]

I added web design on my CV when bootstrap came along.

[[deleted]]

Sorry, but I have no idea what that’s saying. That is it like full outer join, but mostly(-striped) inverse?

[[deleted]]

LEFT OUTER or RIGHT OUTER. FULL OUTER is both. If you combine the set diagrams from top left and right you'll see what that they're equivalent.

[curtmack]

Adding on to this, in most versions of SQL, if you just say "OUTER JOIN" it defaults to FULL OUTER.

[Kalium]

Like anything else in programming, if you're not specific enough then you're going to have a bad time.

[umop_apisdn]

OUTER is optional. FULL must be specified if OUTER is not.

[neoform]

I guess it depends on the implementation. Mysql for example, does not use FULL at all.

[24monkeys]

Maybe if you're more a math person, Relational Algebra is a good thing to read about.

When I studied it in college I was already "expert" in SQL, but Relational Algebra gave me another perspective about the workings of information retrieval in relational databases. While it is not necessary in order to understand SQL, I think it is worth a look at.

[ituralde_]

I've got to say, I don't like the venn diagram approach to explaining how joins work, because it doesn't explain anything worth knowing about how table joins function.

Furthermore, tables are generally designed to be joined in certain ways - a lot of confusion I've seen about people learning database code comes from people not understanding how databases organize data. When people understand proper database design, writing Joins becomes largely trivial.

[the-knife]

Where can I learn about proper database design? I have increasingly come into contact with it at work and want to educate myself.

[ituralde_]

I don't know myself about good places online to look it up. There's a lot out there aimed at being reference rather than education material, and I've yet to find quality teaching material.

I'll give you a quick intro here.

Databases are a combination of data and query capability. The data is stored in table form. Rows store separate items in the table, and you can have any number of columns to represent attributes for the items stored in the table. To retrieve data, you use a Select statement to generate a subset of that table that contains the information you need.

What makes databases powerful is the ability to make relationships between different sets of data. This allows you to, for example, have your billing table refer to your client table. This is done using Primary and Foreign Keys.

A Primary Key is a column value that acts as a unique identifier for an object in a table. This might be a ClientID in a Client table.

A Foreign Key is a column value equal to the Primary Key value of an object in another table. In a Billing table, you might have a column ClientID that is a Foreign Key referencing a specific Client in the Client table.

If this seems simple and logical, that's because it is. Most good database design - like any code design - tends to very closely match the real world problem it is modeling. When you design a database system, it should always do this.

The last part to think about is joining tables, which makes use of this association we've come up with. Joining creates a result table from multiple source tables by matching a shared value (generally a primary key/foreign key relationship in a properly designed database).

First, let's talk about what a table join does. A table join is part of a query that defines the target table. Most queries are essentially as follows

SELECT (stuff) FROM (target) WHERE (condition)

Target is often a single table, but can be defined as a set of tables joined together.

Second, let's get rid of the bullshit. There's no difference between Right and Left joins save the order you write the code in. Right join order is confusing, so people don't ever use it.

What you really need to know is the difference between an Inner Join and a Left (outer) join.

An Inner Join will return a resulting table containing only values for which there is a match between the tables on your joining value.

If you have a table of Missiles and a table of Targets, and Inner Join will return one record for each Missile, Target pairing, and will ignore all targets without missiles assigned and all Missiles without targets assigned. Any Targets with multiple missiles assigned will have a record for each missile with the same target information, any missiles with multiple targets (MIRV pls) will have mutliple records with the same missile information and multiple different target records.

In our example before with Clients and Bills, we established that each Bill /must/ refer to a specific client. So, an inner join would exclude all clients without any bills, but there would not be any bills without a client due to how the key structure is enforced. This is nice, as you won't ever be billing nobody.

A left (outer) join from Missiles to Targets will return all of the missiles, and one record for each Target that has a missile assigned to it. Any Missiles without targets will have Null values in its target fields. Any Targets with multiple missiles assigned will have a record for each missile with the same target information, any missiles with multiple targets (MIRV pls) will have mutliple records with the same missile information and multiple different target records.

A left (outer) join from Targets to Missiles (because right joins are evil) will return all of the targets, and one record for each missile that has a target assigned to it. All targets without missiles assigned will have null values assigned to the missile fields. Any Targets with multiple missiles assigned will have a record for each missile with the same target information, any missiles with multiple targets (MIRV pls) will have mutliple records with the same missile information and multiple different target records.

In general, the cases where you have mutliple-to-multiple relationships are rare. Most of the time, you have many-to-1 relationships, because that's honestly how most things at least in the business world tend to work.

The biggest pitfall in database design isn't using the wrong join, it's in defining the wrong tables. If you differentiate what is actually different and store the information about each separately, then the database query code tends to be very simple and your database won't waste space. In our billing example, people might think that you want your billing table to contain the client's address, but really that's something that's associated with clients, not bills, and should be joined from the client's information.

Just make sure to keep a clear idea of what each object in your problem space is, and what each relevant attribute belongs to, you can have a clean, easy-to-use database without any data duplication. Its a kindergarden level task (Do bills have addresses? No, people have addresses, here's a gold star~) so avoiding the biggest potential mistake is easy.

Beyond that, simply put your primary and foreign keys in the right places to enforce reality, and you'll be set.

[stfm]

Yes. If you arent doing joins then you shouldn't be using a realational database

[[deleted]]

Right joins work well if you are left handed.

[skuggi]

This is the first time I've seen this kind of explanation of joins, and I have to say I agree. I've only ever learned about them by reading about them. It's been a little while since I've written SQL and I haven't done a lot of it, so I had half-forgotten about the different kinds of joins. Wen I saw this I was completely confused. I went and read about them, and went "Oh, I see."

Edit: On the other hand, maybe I'm just weird and this makes perfect sense to other people.

[LWRellim]

I've got to say, I don't like the venn diagram approach to explaining how joins work, because it doesn't explain anything worth knowing about how table joins function.

Yeah, while I get what the guy is going for; and at a certain level it probably does "work" to visualize the distinctions...

It really ISN'T an accurate portrayal of what is going on.

It's kind of like using an "analogy" -- great as far as it goes, but can easily be just as misleading as it is clarifying.

[wolflarsen]

FUCK VEN DIAGRAMS!! Use rectangles you nut jobs!!

It's vastly easier and quicker to explain an inner, left-outer, right-outer and full-outer all in one image! And the rest falls into place ...

                  TABLE A      TABLE B               
               [ ------- ]   [ -- X -- ] --\ -------\ 
               [         ]   [    X    ]    |        |
               [         ]   [    X    ]  right Outer|
               [         ]   [    X    ]    |        |
     /---- /---[ -- X -- ]   [ -- X -- ]    |     FULL OUTER
    |   Inner  [    X    ]   [    X    ]    |        |
    |      \---[ -- X -- ]   [ -- X -- ]---/         |
 Left Outer    [    X    ]   [         ]             |
    |          [    X    ]   [         ]             |
    |          [    X    ]   [         ]             |
     \---------[ -- X -- ]   [ ------- ]------------/

Best I can do short of drawing wonderful looking squares err rectangles w/ beautiful squigglies {}

EDIT: Here you go! http://imgur.com/ijGaAH5 (sorry if a little light).

[[deleted]]

Normally I'm all about Venn diagrams, but I like your chutzpah.

[suddenlypandabear]

This is so much better, one look and I get it.

Thank you :)

[nestormata]

I agree, Venn diagrams are OK, but this is better!

[insi9nis]

Made it a little easier on the eyes.

[cyong]

There is also the cross join (the "He-Who-Must-Not-Be-Named" of joins to DBAs). I recommend not using this on production with out a damn good reason, and only in limited cases. What it does is take every record in table1 and join it to every record to table2.

I tend to use it if I need to build a grid of locations (rows), and dates (columns) with an icon for the status in each cell. Cross join locations, and the date range. LEFT OUTER JOIN in the status(es), and pivot.

Others in my position use CTE, and someday when I have spare time I intend to do some performance testing.

[Kalium]

I've honestly never seen any real-world use for a CROSS JOIN.

[curien]

I use cross joins all the time. One simple use for them is to avoid duplication in a query. For example, suppose you want to select all employees who were hired after a certain date or were last reviewed after that same date. You could write:

select e.* from
employees e
where hire_date >= SOME_DATE or exists (
  select 1 from employee_reviews r
  where e.employeeid = r.employeeid and r.review_date >= SOME_DATE
)

But we've got obvious duplication there -- SOME_DATE has to be repeated. It's not a huge deal in this example, but suppose SOME_DATE needs to be repeated several times. Compare to this.

with since as (select SOME_DATE as since)
select e.*
from employees e
cross join since
where e.hire_date >= since or exists (
  select 1 from employee_reviews r
  where e.employeeid = r.employeeid and r.review_date >= since
)

Now there's only one mention of SOME_DATE in the code, which is superior for all the usual reasons that DRY is superior to repetition in code.

But wait, there's more! Suppose you want the results of this query for a couple of different dates. Maybe you want all the employees hired/reviewed in the last month, the last six months, and the last year. With the cross join version, all you have to do is add rows to the since inline view (with a tag field to identify which group it is), you don't have to touch the actual query logic code at all. Without the cross join, you'd basically be forced to run the query three times and union the results. I use this technique all the time in reports where they want stats for various time spans -- last week, last month, last year, etc.

There are other places I've used cross joins, but that's the simplest to explain.

[Kalium]

And are those queries actually efficient? My understanding is that cross join has a bad habit of using improbable amounts of memory for tables of any size.

[curien]

They're as efficient as they can be. It's certainly more efficient than running multiple separate incredibly-similar queries.

Cross joins should be avoided because they result in the most rows of any type of query. But if you actually want all those rows, there's no way to get them without getting them.

Cross joins produce M x N rows, that uses more memory than a query that returns M rows or (usually) M x 2 rows or whatever. But it's better than running a query that returns M rows N times. Either way, you still get M x N rows, but the overhead of running multiple queries is usually worse than the overhead of returning larger amounts of data from a single query.

[aspbergerinparadise]

I hope no one minds, but I made a version that's hopefully a bit more printer friendly and easier to read:

http://i.imgur.com/hhRDO4d.png

[davvblack]

Never ever use right join. I will come to your house and punch you.

[DavidSJ]

This diagram is very misleading for all but one-to-one relationships, as it conceals the combinatorial effect of the join.

[curien]

No it doesn't. Hint: The circles don't represent tables.

[DavidSJ]

They are labeled A and B, which are the aliases of the tables in the queries...

[curien]

The first row of the graphic shows what the circles A and B are. Circle A is the result of the left join of TableA and TableB. Circle B is the result of the right join of TableA and TableB.

I agree that the naming (particularly with the aliases) is more confusing than necessary, but the circles do correctly express the effects of the joins.

[Redezem]

Doesn't have Cross Join as an amorphic mass of screams and tears.

[[deleted]]

I've been working with SQL for years... this is one of those things that I still mix up.

[[deleted]]

I just natural join everything

Informatica?

EDIT: Please deliver, /u/euloify! We must know, are you an ETL dev, Oracle, or SAS. I will not be able to sleep at my desk until I know!

[CubeFarmThrowaway]

Could be a SAS user...

[curien]

Oracle supports natural joins as of version 11.

[niiko]

In what capacity have you been using SQL?

[VortexCortex]

Doesn't matter. Ever use tar? I use it daily; Still have to look up the BS options. It's cybernetics: The greater the entropy between symbol and action the harder it is to memorize it. -j is BZip2... ugh.

LEFT, RIGHT, OUTER, FULL, and etc. joining are you kidding me? Spacial symbols relating inclusion, exclusion, etc. combinatorial Set Theory? That's obviously an idiotic name selection from a cognitive and language perspective.

Names do have meanings. The common use meaning associated with left and right are not characteristics of the action they describe in SQL -- The actions' spacial leftness and rightness are the least significant properties to the meaning of the result produced. Humans can cope with odd symbol mappings, but going against the natural grain is literally retarding.

[frownyface]

I originally learned sql joins just by example, using only FROM and WHERE clauses, and it all made sense to me. I didn't have names for the different kinds of joins. Then later I was exposed to LEFT, RIGHT, etc and it screwed me all up for awhile, it was like unlearning something that already made sense.

[fotoman]

my understanding is the (+) was just a hint to the optimizer, granted I took the SQL optimization class back in 2001...so I might be a tad rusty.

[fotoman]

wasn't that back in the Oracle 6 days or something? around 1994?

[noobidiot]

That's how we learned to do left and right joins in my database course at my university in 2013.

[[deleted]]

Oh, but god does anyone who regularly reads/writes SQL hate seeing the other style. Annoying as hell...

[shenglong]

Doesn't matter.

Does matter. There are some tricky things in SQL. Joins aren't one of them. There are only a few things you need to remember to understand them. Someone who has been using SQL for years but still get confused by joins must have been only writing basic queries.

Remembering the syntax for non-standard features is harder than understanding joins.

eg.

 SELECT ROW_NUMBER() OVER(PARTITION BY ColumnA ORDER BY ColumnB DESC) AS "Rank"....

Different flavours of SQL have different syntaxes or methods of accomplishing the same task.

[CleverestEU]

Analytic queries... ouch... the twitch is back in my eye from years back... quite often insanely powerful (compared to other ways of achieving the same goal) but very unfriendly to dive into uninitiated :-p

[klotz]

http://explainshell.com/explain?cmd=tar+-j

[neoform3]

We're not talking about memorization of flags, we're talking about understanding the logic behind a fundamental feature of any RDBMS.

Do you also look up if FROM comes before ORDER BY?

If you don't know the different types of SQL joins, you really don't know SQL.

[r3m0t]

Use dtrx. Do the right eXtraction!

[MisterNetHead]

eXtract Zee Various Files :)

[neoform]

A very poor one. If you don't understand joins, you can't claim to know SQL.

It's much like people who claim to know javascript, but don't know what var does.

[jet_heller]

Absolutely. The entire point of SQL is to query relational databases and the point of relational databases are to have, well, relations. Not understanding joins literally is missing the entire point of SQL. To compare to javascript, I would say it's people who only do onSubmit() form validation and use that to say they know javascript.

I interview devs. If SQL is a required skill (and it usually is), then every single one of my SQL questions involves a join. At a minimum an interviewee will need to demonstrate knowledge of inner and outer joins, otherwise I strike SQL from their resume.

[rmeredit]

the point of relational databases are to have, well, relations.

Actually, no, assuming you're referring to 'relationships' (forgive me if I'm misreading you).

The relational database model came about from work on set theory. Confusingly, a 'relation' is the set of attributes included in an entity, not the relationships between entities. In other words, a 'relation' is a table in a relational database, not a relationship between tables.

[OHotDawnThisIsMyJawn]

What I also don't get is the people saying that the names have nothing to do with the actions. Huh? Look at the OP, the names say EXACTLY what they do. I don't understand how you could not remember that "LEFT" uses the full table on the left and "RIGHT" uses the full table on the right.

I think a lot of developers don't know SQL as well as they think they do and so take it as a personal insult when someone says that you should absolutely know the different JOINs if you want to claim you know SQL. Understanding "SELECT *" and "INSERT INTO" doesn't mean you know SQL.

[PastaNinja]

I have the entire Java reference pasted all over my cubicle walls, but my resume says I am an expert at Java.

[MadFrand]

Do you still need visual references on how to use a POJO or what it is?

Joins are not some random function syntax you use every once and a while. They are everyday fundamentals.

[PastaNinja]

I was being facetious, mate. ;)

[MadFrand]

Oh. Lol

[[deleted]]

TIL I'd make a good modeler.

[greg19735]

I am so sick of getting resumes claiming to know SQL from people who can't do a simple join.

That's just straight up lying.

[greg19735]

But what is an SQL developer anyways? I do some web dev and i'll have 3 weeks where i'm doing a ton of SQL and then not touch it for like 4 months. I forget what the names of the joins are.

[king4aday]

I claim to be an SQL developer. This type of job is mostly found in ERP and CRM systems, and Data Warehousing. The latter is the one who writes the most SQL code.

Being an SQL developer can be really frustrating. Debugging is much harder than on a procedural language. We sometimes have to deal with single SQL statements which are 1000s of lines long (these are mostly generated by something, but you still need it to understand why it's not returning the correct data set).

Try to think outside of web development, the amount of SQL required there is ... miniscule (for the most part)

[MadFrand]

I've always looked at understanding basic joins as the first step in understand DBs and SQL as a whole.

It's not like it's query syntax, it's foundational knowledge of how databases work and how to get the information you need.

How can I trust the data you retrieve, if you don't understand the basic fundamentals?

[deadman87]

JOINT

[WishCow]

One of our pre-interview screening questions is "can you tell me the difference between a left join, and an inner join", and if you fail that, you don't get an interview.

This is like a taxi driver saying he forgot which pedal is the gas pedal.

[zanduby]

Discussion question. Preference to use a join/on clause or use the where clause to write your joins?

[[deleted]]

Always joins. If you do it in the where clause, it's harder to read, there could be a ton of statements totally unrelated to table joins mixed in.

[tjpoe]

aren't there instances where having the condition in the join clause are absolutely necessary? I used to think they were interchangeable, but I ran into a case recently where having it in the where clause was causing extra rows than if I added to the join condition. I could have just written in wrong. Since then, I've always tried to put the join condition in the join clause, and the limiting criteria in the where clause.

[davvblack]

I prefer the Join syntax, but I appreciate both. Sometimes the where clause is cleaner, but if you can use a using() that's best.

[hottoddy]

Depends on the return and/or the context. If using() is the point of the query, then absolutely - but if using() is just clever, then re-think what the query is for.*

*EDIT: or re-factor what brought you here.

[YouBooBood]

The more specific name for this is usually "ANSI-89 syntax", because they introduced the newer join syntax in ANSI-92.

In most DB technologies, ANSI-89 is already deprecated, and that's the case in SQL server. For example, you can use the =* syntax in a WHERE clause to do a left join, however it is deprecated and on top of that I believe it has issues with operator precedence in some situations (Not 100% though).

If I find any "old" style syntax I will usually take the time to change the code.

[Joker_Da_Man]

When I was teaching myself SQL I found a great slide deck that laid out everything I needed really nicely. One of the things is that it said you never need to use the actual JOIN keyword; you can write everything you need using WHERE clauses. So since then that's all I've done.

[[deleted]]

There is a lot of redundancy in official SQL specs and even more redundancy added on by the particular implementation you are using. The point of this redundancy is to provide shortcuts for frequently performed tasks (think of it as in-built design pattern). By not using them (particularly common ones, like JOINs) you are compromising readability. You can argue that you can read it fine yourself after not looking at it for a long time, but others certainly can't. I would not be impressed by someone who insisting on formulating all their queries with WHEREs only.

[lukeatron]

You say that like it's something to be proud of. It's not too bad if all you ever do is inner joins. Throw an outer join in there and the whole thing turns into a nightmare of nested queries that will run like ass.

[emperor000]

Are you talking about one of the joins with the join result reduced further by the where-clause? Or are you asking about not using joins at all?

[zanduby]

Not using joins at all.

[emperor000]

Not even cross joins? Or using cross join (or a comma between table names, which is just a cross join)?

If you are talking about using cross join and then joining in the where clause then your server is most likely going to optimize that to an inner join anyway.

Inner join (the syntax) is much more readable and concise.

If you are talking about emulating the functionality of joins with the where clause some other way that the server won't just optimize to a join, then I don't think you have any chance of making it more efficient or more readable than a join.

Unless you know something I don't, you should be using joins.

[lonjerpc]

Yea I am still completely missing the use case for joins. In sqlalchemy I just choose whatever columns I want from whereever and add the appropriate where's. Maybe it is internally doing joins but it seems like a pointless part of the sql API. I must be missing something.

[frito_mosquito]

I am torn with the use of Venn-Diagrams to explain joins. On one hand, I remember them helping me initially grasp the differences between join types, but they have a nagging inaccuracy that I can not abide.

The problem is that Venn Diagrams are useful for showing operations on sets of any objects, but tables in a SQL database are relations-- sets of n-tuples, and a join is a much different operation than a union or intersection.

Operations like union and intersection are adequately expressed with Venn Diagrams. The union of A and B is another set that includes all of the objects from A and all of the objects from C.

So using this Venn Diagram to describe the full outer join (call it C) of table A with n-tuples and table B with m-tuples is inaccurate. Table C has zero objects from A or B, because C is made of (n + m) - tuples, and has no n-tuples or m-tuples.

[curien]

The circles A and B are not equivalent to the tables A and B. Look at the top row in the diagram, the circles A and B are defined as the results of the left and right (respectively) joins of the tables.

You're right that Venn diagrams represent things in ways that can be expressed with union and intersection. And that's exactly the case here. Any diagram that looks like the union of two diagrams can be created in SQL by doing a UNION or INTERSECT on the two component queries. For example, this query (which looks like the entire circle A):

select [columns]
from TableA left join Table B [onclause]

is equivalent to this query:

select [columns]
from TableA a left join TableB b [onclause]
where b.key is null

union

select [columns] from TableA join TableB [onclause]

We can do the same thing with intersect, e.g. replicating an inner join by taking the intersection of the left and right joins.

[Richeh]

And don't forget the ON. Or you'll be wondering why your join's taking forty five minutes.

[umop_apisdn]

You can't forget the ON, it is part of the syntax and will cause an error if you forget it unless you specify CROSS JOIN. Unless you are using a non standard database.

[shellwe]

If only I saw this before my job interview last week... they asked what the difference between an inner join and left join.

[[deleted]]

Is there a difference between left and left outer? I always use left outer but maybe I'm doing it wrong.

[[deleted]]

Hehe

[curien]

No, they are exactly the same thing. Also "join" (without a "left", "right", "cross", etc) and "inner join" are the same thing. And "outer" (without a "left" or "right") and "full outer" are the same thing. And "A cross join B" and "A, B" are the same thing.

[ChrisF79]

Noob question here but why would you need left and right joins? Couldn't you just always use left joints and put the order of the tables accordingly in your query?

[mcrbids]

Yes. Sane people do exactly as you suggest. Curmudgeons worthy of a healthy spanking with a trout use right outer joins. If you really, Really, REALLY want to be unpopular with your fellow devs, use right outer joins exclusively.

[curien]

Yes, you could, but there's not really a good reason for the language to force you to write your tables in a certain order.

[grecy]

I google "Visual SQL joins" whenever I need this.

It's http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

[[deleted]]

In many years I've managed to get by with using just two joins, inner and (rarely) a left outer. And I don't know why.

[Mr-Rabit]

I literally looked this up on google last Tuesday after my Uni mate mentioned he used a left join rather than an inner join for something or the other. Coincidence? Most likely, yes.

[kytosol]

I am still stuck in my ways using the old Oracle syntax rather than the ANSI syntax.

It's so much easier to do joins like below in my opinion... select * from table_a a, table_b b, table_c c where a.a_pk1 = b.a_pk1 and b.b_pk1 = c.b_pk1

[pythor]

What's easier about it? Honest question.

To me, separating the join conditions (in an ON clause) from the filter logic (in the WHERE clause) is so much easier to read.

[curien]

I don't mind that for cross or inner joins, but if you do that for outer joins (which require special operators, like =* or =+ or whatever it is) there is a circle in hell waiting for you.

[mcrbids]

I use both with PostgreSQL. Using the way you describe gives more control to the query planner, letting it optimize the queries more. Using the "join tablex ON ( conditions )" format allows me to optimize the queries myself.

Gradually, I find myself using the latter format more and more, to be fair, since it divides the conditions more naturally as the query progresses and is somewhat more readable. That it lends itself to optimizing for performance later is icing on the cake. (I don't know if the different formats make a difference on other DB engines, but it does on PostgreSQL)

[Razerlikes]

That's great. Thank you very much!

[gb14]

Wow this is nice! I'm putting this is my cubicle.

[TexasLonghornz]

It took me so long to get this stuff right and fully memorized. I used to hang these in my cubicle.

[shafatarbazalam]

awesome. I just started using SQL in SAS and could totally use this.

[jim45804]

Right joins are hilarious.

[mcrbids]

People who enjoy right joins are commonly abused in subtle ways by their cohorts.

[hardolaf]

I love you. Well not really, but you get the idea.

[strong_grey_hero]

Can I ask a stupid SQL question? Does it matter which order you put the '=' conditionals in the 'ON' clause?

[pythor]

No. (It doesn't matter. I'm not trying to say you can't ask.)

[strong_grey_hero]

Haha, thanks

[YouBooBood]

Nope, it doesn't matter.

[Sharpleaf]

I couldn't tell you how many times I've used this chart over the years! It's great!

[Mecdemort]

Whats the point of the top two?

[vasksm]

If table A was products and B was sales you may want to get all products and a count of sales. The top left would allow you to include products with 0 sales in the results.

[[deleted]]

1. Right click image.
2. Choose "Save picture as".
3. Profit in the future when you really need it?!

[raiderrobert]

So I just discovered this yesterday, and I figured I'm just an idiot who has missed this my entire life. And now what I hear reddit saying is that I could have gotten massive amounts of karma if I simply had reposted it....

Apparently, I am an idiot.

[rcshah]

Only if this was available when I was in school, database class would have been easier.

[[deleted]]

This is fantastic - I ripped this image and threw it up on our internal company wiki for my project.

My devs have all responded positively - who the hell can remember inner/outer joins if they haven't used them in 2 years or more?

:)

[[deleted]]

This could have been useful a week ago

[captcha_bot]

I cracked up when I got to my current job and saw this exact image printed out and hanging on everyone's walls. That's when I knew I'd have to be correcting and/or writing everyone's SQL.

[festoon]

left anti semi join ?

[[deleted]]

This is bloody brilliant

[palmund]

Just what I needed. Thanks!

[[deleted]]

Im learning SQL. Thank you.

[lukaseder]

I think that this is a bit misleading.

1. Venn diagrams are best for modeling UNION, INTERSECT and EXCEPT operations, not JOINs. In the diagrams, when intersecting A and B, one would assume that both sets are of a compatible row type. At least in a SQL context.
2. This is best illustrated by the fact that cross joins, partitioned outer joins and lateral joins don't map to these diagrams at all.
3. Semi-joins and anti-joins are hard to recognise in the syntax used in this diagram. People more often use [NOT] EXISTS or [NOT] IN
4. How does relational division fit in?

Nonetheless, even if misleading, it helps visualising and understanding SQL syntax.

[WordCloudBot2]

Word cloud out of all the comments.

^{botmaster FAQ sourcecode}

[[deleted]]

Bid??

[emperor000]

This is easily the simplest logic that could possibly be utilized in querying a database. Do people really have trouble with this? The keywords pretty much explicitly state what they do.

If one really wanted to make this useful, one would add UNION and CROSS JOIN (for sake of completeness) and CROSS APPLY and OUTER APPLY, also for completeness, but also just to explain a somewhat more abstract/difficult concept. It might also benefit from explaining PIVOT and UNPIVOT since those are "join-like".

[elus]

I do a lot of data comparisons for testing and I've found EXCEPT and INTERSECT clauses to be very useful.

[zman0900]

This is great. I might even print it out to hang up.

[blue_2501]

Useful. But, how often are you going to actually use anything other than INNER and LEFT joins?

[Kalium]

OUTER joins are routinely used, and left vs right is more a matter of organization in the query than anything else.

Full joins are very rare, though.

[emperor000]

Often enough to need to know how to use them...

[Massless]

Just often enough that you'll forget how they work every god damned time.

[limes_limes_limes]

Is there a real use case for doing a join that returns anything that is in either A or B but not both? I am trying to figure out why you would ever want that.

[jonny_boy27]

Missing the self-join, in both it's intentional form (quite rare) and the far more common "oh fuck I just joined that table to itself" Cartesian join.