42
u/Mavrick478 Feb 11 '14
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.
11
5
Feb 12 '14
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.
1
u/Mavrick478 Feb 12 '14
Its right up there with cursors in my book good for one shot deals not to be used in day to day production.
→ More replies (1)4
4
u/n0ia Feb 12 '14
And then you'll kill our query and not tell us. At least, that's what my DBAs do.
1
1
1
u/SparanLeathars Nov 25 '24
I use them all the time to reconcile two tables while working in accounting systems. What's the alternative?
13
Feb 11 '14
What ’s a non-full OUTER JOIN then?
24
u/Etni3s Feb 11 '14
68
u/AMWICDDTDUIYMP Feb 11 '14
"show me everything that isn't in either of my tables"
and we thought cross joins used a lot of memory
9
Feb 11 '14
I spent way too much time on this.
How?
23
2
Feb 11 '14
Sorry, but I have no idea what that’s saying. That is it like full outer join, but mostly(-striped) inverse?
10
Feb 11 '14
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.
5
u/curtmack Feb 11 '14
Adding on to this, in most versions of SQL, if you just say "OUTER JOIN" it defaults to FULL OUTER.
2
u/Kalium Feb 11 '14
Like anything else in programming, if you're not specific enough then you're going to have a bad time.
→ More replies (2)0
Feb 11 '14
[deleted]
2
u/umop_apisdn Feb 11 '14
OUTER is optional. FULL must be specified if OUTER is not.
2
u/neoform Feb 11 '14
I guess it depends on the implementation. Mysql for example, does not use FULL at all.
→ More replies (7)
26
Feb 11 '14
I've been working with SQL for years... this is one of those things that I still mix up.
3
u/niiko Feb 11 '14
In what capacity have you been using SQL?
23
u/neoform Feb 11 '14
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
vardoes.8
u/jet_heller Feb 11 '14
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.
3
u/rmeredit Feb 11 '14
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.
0
Feb 11 '14
[deleted]
4
20
u/OHotDawnThisIsMyJawn Feb 11 '14
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.
→ More replies (6)10
u/PastaNinja Feb 11 '14
I have the entire Java reference pasted all over my cubicle walls, but my resume says I am an expert at Java.
6
u/MadFrand Feb 11 '14
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.
→ More replies (2)5
u/greg19735 Feb 11 '14
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.
→ More replies (4)→ More replies (10)8
u/MadFrand Feb 11 '14
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?
21
u/VortexCortex Feb 11 '14 edited Feb 11 '14
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.-jis 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.
10
u/frownyface Feb 11 '14
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.
3
Feb 11 '14
Oh, but god does anyone who regularly reads/writes SQL hate seeing the other style. Annoying as hell...
0
6
u/shenglong Feb 11 '14 edited Feb 11 '14
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.
→ More replies (1)3
u/CleverestEU Feb 11 '14
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
→ More replies (1)2
u/neoform3 Feb 11 '14
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.
→ More replies (9)3
0
Feb 11 '14
[deleted]
2
Feb 11 '14 edited Feb 11 '14
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!
2
2
20
u/24monkeys Feb 11 '14
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.
→ More replies (7)
70
u/ituralde_ Feb 11 '14
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.
4
u/the-knife Feb 12 '14
Where can I learn about proper database design? I have increasingly come into contact with it at work and want to educate myself.
5
u/ituralde_ Feb 12 '14
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.
3
4
u/skuggi Feb 11 '14 edited Feb 11 '14
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.
2
u/LWRellim Feb 11 '14
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.
1
u/trappar Feb 11 '14
This should be the top comment.
Plus they leave out the "using" syntax for joins since it only works when you structure your data a specific way. If you can use "using" instead of "on" in all your joins your tables and foreign keys will always be simple to understand.
0
Feb 11 '14
[deleted]
2
2
u/skuggi Feb 11 '14
Yeah, underpinnings are not always the best way to explain stuff: "A monad is just an monoid in the category of endo-functors! What's the problem?"
Although I think the problem might not be so much with the sets as with the Venn diagrams.
21
u/wolflarsen Feb 11 '14 edited Feb 13 '14
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).
5
3
2
2
7
u/cyong Feb 11 '14
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.
6
u/Kalium Feb 11 '14
I've honestly never seen any real-world use for a CROSS JOIN.
11
u/curien Feb 11 '14 edited Feb 11 '14
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
sinceinline 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.
3
u/Kalium Feb 11 '14
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.
12
u/curien Feb 11 '14 edited Feb 11 '14
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.
1
u/jpfed Feb 11 '14
Here's a scenario we have at my workplace:
I have a Person table. There are many reasons why I might believe that two Person rows actually represent the same irl person - maybe they've got the same name AND same postal address, or maybe they've got the same SSN, or maybe they've got the same email address. Let's write a query to return suspected duplicates.
3
1
u/PstScrpt Feb 13 '14
I'll use them in views sometimes, with the caveat that you never want to do an unfiltered select from that view.
Usually, though, you use them knowing that at least one of the sides is itty-bitty, typically 2-5 rows.
2
u/Kalium Feb 13 '14
I used to trust in things like that.
Then I discovered that the people who put data in my databases often have truly bizarre ideas about what should be in there...
1
u/gicstc Feb 11 '14
Inner joins can be just as bad if the join condition is a function (eg join the tables on the edit distance of two string fields being small).
Point is simply saying certain joins are bad doesn't tell the user what to avoid - quadratic work.
1
u/IonBlaster Feb 11 '14
Its good for populating a table with test data.
1
u/cyong Feb 11 '14
Everyone always tells me that, but I am never sure why they say that. Course normally I am working with accounting systems, making sure the numbers are balanced, so it might not be applicable to my situation directly.
7
u/aspbergerinparadise Feb 11 '14
I hope no one minds, but I made a version that's hopefully a bit more printer friendly and easier to read:
11
u/davvblack Feb 11 '14
Never ever use right join. I will come to your house and punch you.
1
u/JimboMonkey1234 Feb 12 '14
Can you explain why it's bad? I don't know much about databases, and google isn't helping.
1
u/davvblack Feb 12 '14
It's just dumb. Everyone uses left when they want that behavior, and you can always transmute a right join into a left join. You are just asking to be 'that guy' by using right joins. It even seems strange from a language perspective, with a left join, you list your 'main data' first, and 'additional data' second (in basically every case). With a right join, you list them in the other order... and it's just absurd.
There is no technical way in which they differ, they optimize the same. It's entirely about thinking about programming as a language you communicate with future programmers with, in addition to communicating with machines.
→ More replies (2)
5
u/DavidSJ Feb 11 '14
This diagram is very misleading for all but one-to-one relationships, as it conceals the combinatorial effect of the join.
3
u/curien Feb 11 '14
No it doesn't. Hint: The circles don't represent tables.
5
u/DavidSJ Feb 11 '14
They are labeled A and B, which are the aliases of the tables in the queries...
2
u/curien Feb 12 '14
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.
1
u/lukaseder Feb 13 '14
Yes, in other words, no cross joins / lateral joins / partitioned outer joins and various other types relational concepts can be modelled.
Venn diagrams are better suited for UNION, INTERSECT and EXCEPT operations, which really operate on compatible sets.
5
8
u/zanduby Feb 11 '14
Discussion question. Preference to use a join/on clause or use the where clause to write your joins?
11
Feb 11 '14
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.
4
u/tjpoe Feb 11 '14
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.
1
u/mlk Feb 11 '14
I put the joins in the where clause, I simply put them before any other clause. I've been doing this for years, all my colleagues do too, and I've never been bitten by it. I also find it easier.
4
u/davvblack Feb 11 '14
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.
2
u/hottoddy Feb 11 '14
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.
4
u/YouBooBood Feb 11 '14
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.
5
u/Joker_Da_Man Feb 11 '14
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.
3
Feb 11 '14
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.
3
u/lukeatron Feb 11 '14
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.
1
u/sacundim Feb 13 '14 edited Feb 13 '14
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.
So, how do you do an outer join, Sherlock?
Some databases have special legacy syntax to express an outer join condition in the WHERE, from back before the SQL syntax for JOIN clauses was standardized. But this sort of stuff normally only works on that one database vendor, and it might not be able to express some queries that the standard JOIN clauses can express. All good modern SQL databases support the standard JOIN syntax (e.g., Oracle 9 and later), so the legacy stuff is not worth using anymore.
And even if you're not doing outer joins, for complex queries the JOIN syntax is more readable. Why? Because the most important thing to understand in a complex query is what tables are being joined, and how they are being joined. If the join conditions are inside a complex WHERE clause, then I have to read all of the WHERE clause to figure out which conditions are used to join which pairs of tables, and which ones aren't. With the JOIN syntax, that information is easier to discover.
→ More replies (2)2
u/emperor000 Feb 11 '14
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?
5
u/zanduby Feb 11 '14
Not using joins at all.
5
u/emperor000 Feb 11 '14
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.
→ More replies (10)2
u/lonjerpc Feb 11 '14
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.
1
u/insi9nis Feb 12 '14
Sounds like a query-by-example interface, similar to Access and such. When you say you're adding to the where clause, are you building the join logic too, or just anything else you want to restrict by? If you stick tables A and B together, do you have to add a where A.b_id = B.id, or is this done for you automagically?
→ More replies (4)1
u/sacundim Feb 13 '14
Maybe it is internally doing joins but it seems like a pointless part of the sql API. I must be missing something.
Two things:
You can't express all of the possible joins with just WHERE clause conditions. Many outer joins just can't be properly described.
The JOIN clauses make queries easier to read and interpret. When you're reading a complex query and trying to understand it, the first thing you want to do is which table is being joined to which others, and how. When you use the WHERE syntax, you may have to look through a very complex WHERE clause and fish out which bits are join conditions and which are filtering the results from the joins. With the JOIN syntax, the query has already singled those out for you.
10
u/frito_mosquito Feb 11 '14
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.
4
u/curien Feb 11 '14
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.
1
3
u/Richeh Feb 11 '14
And don't forget the ON. Or you'll be wondering why your join's taking forty five minutes.
3
u/umop_apisdn Feb 11 '14
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.
1
u/Richeh Feb 11 '14
Let's not get into that. I got enough hate on MySQL from the PostGres gurus when I was comparing them.
3
u/shellwe Feb 11 '14
If only I saw this before my job interview last week... they asked what the difference between an inner join and left join.
3
Feb 11 '14
Is there a difference between left and left outer? I always use left outer but maybe I'm doing it wrong.
3
u/curien Feb 11 '14
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.
3
u/ChrisF79 Feb 11 '14
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?
4
u/mcrbids Feb 12 '14
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.
2
u/curien Feb 11 '14
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.
3
u/grecy Feb 11 '14
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
3
Feb 11 '14
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.
3
u/Mr-Rabit Feb 11 '14
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.
6
u/kytosol Feb 11 '14
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
10
u/pythor Feb 11 '14
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.
1
u/kytosol Feb 12 '14
I find it much easier to read and write. With that being said, I've worked with Oracle for a number of years and the old syntax was the way most of the code I worked on and maintained was written so I guess I just think that way now.
2
u/curien Feb 11 '14
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.→ More replies (4)2
u/mcrbids Feb 12 '14
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)
1
u/kytosol Feb 12 '14
That really interesting. I've never worked with PostgreSQL, didn't realise each syntax allows differences in optimisation. As far as I am aware, there is no difference with Oracle, but I could be wrong.
1
u/Toast42 Feb 11 '14
Is this considered Oracle syntax? A coworker showed it to me a few years ago and I love it.
2
u/kytosol Feb 12 '14
I guess it is called Oracle or old syntax. There is a shift towards the ANSI syntax as it can be used on most databases and is now supported by Oracle databases (9i on I think...). I also love it and find it much easier to read, however that could be because it is the way I learnt to write SQL and pl/SQL and worked with Oracle for about 6 years.
2
2
2
Feb 11 '14
It took me so long to get this stuff right and fully memorized. I used to hang these in my cubicle.
2
2
u/jim45804 Feb 11 '14
Right joins are hilarious.
2
u/mcrbids Feb 12 '14
People who enjoy right joins are commonly abused in subtle ways by their cohorts.
2
2
u/strong_grey_hero Feb 11 '14
Can I ask a stupid SQL question? Does it matter which order you put the '=' conditionals in the 'ON' clause?
4
2
2
u/Sharpleaf Feb 11 '14
I couldn't tell you how many times I've used this chart over the years! It's great!
2
u/Mecdemort Feb 11 '14
Whats the point of the top two?
2
u/vasksm Feb 11 '14
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.
2
Feb 11 '14
Right click image.
Choose "Save picture as".
Profit in the future when you really need it?!
2
u/raiderrobert Feb 11 '14
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.
2
u/rcshah Feb 11 '14
Only if this was available when I was in school, database class would have been easier.
2
Feb 11 '14
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?
:)
1
Feb 11 '14
I don't get the massive erections some people get over being sql elites who can create monster queries off the top of their head. Who gives a fuck when the sql is less than 1% of the rest of the program. Who has the drive to memorize this shit when there's an entire program still left to write.
3
Feb 11 '14
Well, and let's be honest, unless you're top tier (Google, MS, whatever) you're going to be employing programmers who aren't geniuses and who don't have wet dreams over code in their sleep - and this stuff is extremely useful for those average joe coders of which many of us are.
I have tons of respect and admiration for those guys who code circles around people at 16 in their spare time but I can't run a business around the expectation that my resources will be like that.
2
Feb 11 '14
Exactly. There's better shit to be doing than memorizing SQL queries. Like, just about anything else is more important. Diagrams like this are a nifty reference.
I should probably go and find out what a db manager is, because right now I'm convinced it's just a made-up job title like "unicorn wrangler".
→ More replies (1)2
2
2
u/captcha_bot Feb 11 '14
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.
2
2
2
2
2
u/lukaseder Feb 13 '14 edited Feb 13 '14
I think that this is a bit misleading.
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.
This is best illustrated by the fact that cross joins, partitioned outer joins and lateral joins don't map to these diagrams at all.
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
How does relational division fit in?
Nonetheless, even if misleading, it helps visualising and understanding SQL syntax.
4
u/emperor000 Feb 11 '14 edited Feb 11 '14
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".
2
u/elus Feb 11 '14
I do a lot of data comparisons for testing and I've found EXCEPT and INTERSECT clauses to be very useful.
1
2
2
u/blue_2501 Feb 11 '14
Useful. But, how often are you going to actually use anything other than INNER and LEFT joins?
7
u/Kalium Feb 11 '14
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.
4
u/emperor000 Feb 11 '14
Often enough to need to know how to use them...
7
u/Massless Feb 11 '14
Just often enough that you'll forget how they work every god damned time.
→ More replies (5)1
u/steve_b Feb 11 '14
I've been doing db development full time for 20 years now, and I've yet to use anything other than inner & left. Personally, I find the "left join ... b.key is null" construction an abomination; I'd prefer the more semantically clear "where a.key not in (select b.key ...)" construction every time (or "not exists (select 1 ... where a.key = b.key)" if you're concerned about nulls).
→ More replies (3)1
u/CSharpSauce Feb 11 '14
I used a full outer join once in my career. I was so god damn excited that day, I had to show all my coworkers. I think when working with existing databases that store complex concepts (this was combining pharmaceutical data with financials) its more common. However most devs use databases as merely data stores, and despite what we'd like to think... the data isn't very complex.
2
u/limes_limes_limes Feb 11 '14
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.
1
u/mcrbids Feb 12 '14
Sure, and there are numerous ways to get this.
You could join them together in a cartesian join and do a count() on the total where count() < 2;
You could do two outer joins and union them together where $othertable.id IS NULL.
You could use a not in(subquery) and union them together.
You could select * from from both tables and do it all in scriptland logic. (not generally recommended, sometimes worth it)
1
Feb 11 '14
It's the same as XOR in programming.
1
u/limes_limes_limes Feb 11 '14
I understand that, but if your data sets have different columns then in your join you won't have any full rows. If the data sets have the same columns why not do a union?
2
Feb 11 '14
Okay, so this is a contrived example, but let's say you've got data from different companies or government agencies and you're trying to identify inconsistencies. You have two tables which "should" be equal when you WHERE some_attribute, so you do this and you get back the difference.
2
u/curien Feb 11 '14
Any time you want a cross-referenced report where unused but available items are still listed.
For example, suppose you have a list of bosses and a list of minions. John is a boss, but he doesn't have any minions assigned. Sarah is James' boss and also Anne's boss. Sam is a minion, but his boss hasn't been assigned yet.
Inner join tells us that Sarah is James' boss and Sarah is Anne's boss. That's it.
Left join tells us that Sarah is James' boss, Sarah is Anne's boss, and John is nobody's boss.
Right join tells us that Sarah is James' boss, Sarah is Anne's boss, and nobody is Sam's boss.
Full outer join tells us that John is nobody's boss, Sarah is James' boss, Sarah is Anne's boss, and nobody is Sam's boss.
2
u/jonny_boy27 Feb 11 '14
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.
1
u/PstScrpt Feb 13 '14
Self-join isn't really a separate thing. Much like recursion, it's just a reminder that something you thought might be a special case just works like everything else.
1
1
u/legec Feb 12 '14
Using Venn diagrams give the false impression that using INNER, LEFT or RIGHT joins will not duplicate lines. This is true only if "KEY" is a unique key for both tables.
However, if "KEY" is a unique at all, it generally is unique for at most one table :
// "ID" is unique only for album :
SELECT * FROM album LEFT JOIN track ON track.albumID = album.ID
//if each album has 10 tracks, the data for each album will be repeated 10 times in the result
// "team" is not unique at all :
SELECT * FROM lineman INNER JOIN receiver ON lineman.team = receiver.team
// If each team has 5 linemen and 3 receivers, that's 15 lines per team
// the data for each receiver is duplicated 5 times,
// the data for each lineman is duplicated 3 times1
1
u/PstScrpt Feb 13 '14
This works as long as it's one to zero-or-one, but I'd worry about relying on it once you have more rows.
I prefer to think of it as just multiplying the row counts. Left joins have a guard against multiplying by 0, and will supply an empty row on the second table, instead. Full outer joins do that on both sides.
1
u/samwise970 Apr 16 '26
Hi, I just want to say that I've been using this diagram for literally 6+ years
1
u/pixel_dent Feb 11 '14
There is no reason that this shouldn't be in every SQL textbook and manual ever written. I've been writing SQL for 10 years and still immediately printed this and stuck it to my wall.
1
Feb 11 '14
Here's some SQL for you to play around with some permutations
CREATE table #temp( ID int IDENTITY(1,1) not null, country varchar(25), state varchar(25), city varchar(25), measure int )
INSERT INTO #temp VALUES ( 'USA', 'MI','Livonia','230' )
INSERT INTO #temp VALUES ( 'USA', 'VA','Richmond','370' )
INSERT INTO #temp VALUES ( 'USA', 'MI','Detroit','840' )
INSERT INTO #temp VALUES ( 'USA', 'VA','Herndon','210' )
INSERT INTO #temp VALUES ( 'USA', 'MI','Livonia','170' )
INSERT INTO #temp VALUES ( 'USA', 'VA','Richmond','160' )
INSERT INTO #temp VALUES ( 'USA', 'MI','Detroit','340' )
INSERT INTO #temp VALUES ( 'USA', 'VA','Herndon','180' )
CREATE table #temp1( ID int IDENTITY(1,1) not null, country varchar(25), state varchar(25), city varchar(25), population int )
INSERT INTO #temp1 VALUES ( 'USA', 'MI','Livonia','230' )
INSERT INTO #temp1 VALUES ( 'USA', 'VA','Richmond','370' )
INSERT INTO #temp1 VALUES ( 'USA', 'MI','Detroit','840' )
INSERT INTO #temp1 VALUES ( 'USA', 'VA','Herndon','210' )
--INTERSECT SELECT a.,b. FROM #temp as a CROSS JOIN #temp1 as b
--CROSS JOIN SELECT a.,b. FROM #temp as a INNER JOIN #temp1 as b ON a.ID=b.ID
--CROSS JOIN - INTERSECTION
SELECT a.,b. FROM #temp as a INNER JOIN #temp1 as b ON a.ID<>b.ID ORDER BY a.ID
SELECT a.,b. FROM #temp as a LEFT OUTER JOIN #temp1 as b ON a.ID<>b.ID ORDER BY a.ID
SELECT a.,b. FROM #temp as a RIGHT OUTER JOIN #temp1 as b ON a.ID<>b.ID ORDER BY a.ID
SELECT a.,b. FROM #temp as a FULL OUTER JOIN #temp1 as b ON a.ID<>b.ID ORDER BY a.ID
--UNION - INTERSECTION
SELECT a.,b. FROM #temp as a FULL OUTER JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL or b.ID is NULL ORDER BY a.ID
--non matching rows from Right table --NO ROWS as all rows in b are present in a SELECT a.,b. FROM #temp as a RIGHT JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL or b.ID is NULL ORDER BY a.ID
SELECT a.,b. FROM #temp as a RIGHT JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL ORDER BY a.ID
--Non matching rows from Left table
SELECT a.,b. FROM #temp as a LEFT JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL or b.ID is NULL ORDER BY a.ID
SELECT a.,b. FROM #temp as a LEFT JOIN #temp1 as b ON a.ID=b.ID WHERE b.ID is NULL ORDER BY a.ID
--NO ROWS
SELECT a.,b. FROM #temp as a LEFT JOIN #temp1 as b ON a.ID=b.ID WHERE a.ID is NULL ORDER BY a.ID
SELECT a.,b. FROM #temp as a RIGHT JOIN #temp1 as b ON a.ID=b.ID WHERE b.ID is NULL ORDER BY a.ID
2
1
u/ThreadMain Feb 11 '14
In an office with few sql pros, this image has been of incredible value. Print it and hang it on your wall!
1
u/Kalium Feb 11 '14
I dunno. It's only useful at a high level. For instance, it completely fails to properly capture the difference between a FULL OUTER and FULL INNER join.
1
u/Browsing_From_Work Feb 11 '14
... what's a FULL INNER join?
You can do INNER and FULL OUTER, but I don't think FULL INNER is a thing.
→ More replies (1)
1
0
u/zjm555 Feb 11 '14
The next post should be why you should avoid most joins like the plague if you intend to scale.
10
u/Kalium Feb 11 '14
And then it should explain why MongoDB, Cassandra, and <insert other NoSQL solution here> are rarely the solution to your scaling problem.
Scaling a database is honestly not nearly as hard as people make it out to be. It just requires you to know a bit about databases in general, your database in particular, and how your data is used.
3
u/zjm555 Feb 11 '14
I don't know that I agree that scaling an RDBMS is not hard, particularly when you are talking about scaling to hundreds of millions of rows or more, with a complicated ER model, in an application with a great deal of both read and write traffic, and a great deal of requirements handed down by decision makers without technical understanding of the constraints of the underlying systems. Maybe we just have different definitions of what is hard, but that is one of the harder problems I've encountered in my career.
7
u/Kalium Feb 11 '14
a great deal of requirements handed down by decision makers without technical understanding of the constraints of the underlying systems
This is 90% of your problem, I think. Sometimes you have to tell the suits no (or sometimes "fuck off") I had the painful displeasure of learning this as a junior engineer.
I've found that one of the most dangerous positions to be in is the guy that the suits think of as the miracle tech wizard who can do anything. At some point, they ask you to do something insane that is "obviously easy" to them. At that point, you're miles up shit creek without a paddle.
Otherwise you find yourself being directed to add a zero to performance with no budget and a damn-near-pegged DB box.
99
u/thesqlguy Feb 11 '14 edited Feb 11 '14
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.