标签云

微信群

扫码加入我们

WeChat QR Code

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?


Of the answers & comments & their references below only one actually explains how Venn diagrams represent the operators: The circle intersection area represents the set of rows in A JOIN B. The area unique to each circle represents the set of rows you get by taking its table's rows that don't participate in A JOIN B and adding the columns unique to the other table all set to NULL. (And most give a vague bogus correspondence of the circles to A and B.)

2018年07月23日25分08秒

A lot of answers are already provided but I have not seen this tutorial mentioned. If you know Venn diagrams, this is a GREAT tutorial: blog.codinghorror.com/a-visual-explanation-of-sql-joins For me, it's concise enough to be a quick read but still grasps the entire concept and works all the cases very well. If you don't know what Venn diagrams are - learn them.Takes 5-10 minutes to do so and will help whenever you need to visualize working with sets and managing operations on sets.

2018年07月23日25分08秒

DanteTheSmith No, that suffers from the same problems as the diagrams here. See my comment above re the question & below re that very blog post: "Jeff repudiates his blog a few pages down in the comments". Venn diagrams show elements in sets. Just try to identify exactly what the sets are and what the elements are in these diagrams. The sets aren't the tables and the elements aren't their rows. Also any two tables can be joined, so PKs & FKs are irrelvant. All bogus. You are doing just what thousands of others have done--got a vague impression you (wrongly) assume makes sense.

2018年07月23日25分08秒

Could someone answer this in reference to Time Series indexed data - Inner and Outer joins do often not work in a human/Venn sense when it comes to time-stamps which are different by mere milliseconds.

2018年07月23日25分08秒

yeliabsalohcin Read my comments on this page (and my answer). If there are no duplicate rows then Venn diagrams illustrate the difference between outer & innter joins, so they illustrate outer joins in terms of inner joins & vice versa if you know what they do. But Venn diagrams don't illustrate what either outer join or inner join return in terms of their inputs except when the inputs have no duplicate rows and have the same columns. (If you have a different question, post it as a question. Applications don't matter. "Matching" matters, which depend on data types & modes.)

2018年07月23日25分08秒

It would be good to augment the example by adding another row in table B with value 4. This will show that inner joins need not be on equal no of rows.

2018年07月24日25分08秒

An excellent explanation, however this statement: An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union. isn't phrased accurately. An outer join will give the results of A intersect B in addition to one of the following: all of A (left join), all of B (right join) or all of A and all of B (full join). Only this last scenario is really A union B. Still, a well written explanation.

2018年07月23日25分08秒

Pro answer! Liked it. Btw: I still can't find the difference betweek LEFT JOIN and LEFT OUTER JOIN, etc. . If this is not part of the question I can post a new one :)

2018年07月23日25分08秒

Am I right that FULL JOIN is an alias of FULL OUTER JOIN and LEFT JOIN is an alias of LEFT OUTER JOIN ?

2018年07月23日25分08秒

Ameer, Thanks. Join does not guarantee an order, you would need to add an ORDER BY clause.

2018年07月23日25分08秒

Note: There's no FULL OUTER JOIN in MySQL. stackoverflow.com/questions/12473210/…

2018年07月23日25分08秒

I think this diagram assumes that there are no duplicate Key, meaning Key is unique. If Key wasn't unique, I think the result would have been a cross and the return count would be much higher than the size of A.

2018年07月23日25分08秒

In the FULL OUTER JOIN example with the intersection portion excluded, won't using 'A.Key IS NULL' exclude all rows from A because if the Key is NULL then by definition the row doesn't exist? I'm not seeing how that mechanism achieves the results for the VENN diagram shown.

2018年07月23日25分08秒

KellyS.French See my comments on the question & other answers explaining how to read the Venn diagrams. The zones do not enclose rows from tables A & B. They enclose certain rows made from pairing a row from A and a row from B. Which also aren't even rows of the output of the SELECT statement. The Venn diagram answers are all poor.

2018年07月23日25分08秒

A represents all potential rows coming from tableA, B represents all potential rows coming from tableB. The red zones in the diagram mark which of these rows will actually be included in the result set. Note that there could be (and often are) many, many more potential rows coming from a table than there are actually rows in that table. That's because any row in tableA will be potentially occuring once for each row in tableB that references it. This is called the cartesian product.

2018年07月23日25分08秒

This diagram is a bit misleading for the concept. Read the comments in the post as well.

2018年07月23日25分08秒

ya23: what does you mean by full outer join?

1970年01月01日00分03秒

Except that Jeff repudiates his blog a few pages down in the comments: "The commenters pointing out that the diagrams break down in case of multiple and or duplicate results, are absolutely right. I was actually thinking of joins along the primary key, which tends to be unique by definition, although the examples are not expressed that way. Like the cartesian or cross product, anything that results in more rows than you originally started with does absolutely breaks the whole venn diagram concept. So keep that in mind."

2018年07月23日25分08秒

philipxy yes the Venn diagram analogy only works for the primary key after ignoring NULLs- this is often not stated. The Venn diagrams don't help to explain how the other columns in the resulting set are filled with NULLs.

2018年07月23日25分08秒

Galax Unfortunately, whether the joins are on keys (let alone primary keys) doesn't help the diagrams to make sense. (Even though I quoted Jeff saying that he thinks it does.) (I don't understand your "works for the primary key after ignoring NULLs" or "the other columns in the resulting set are filled with NULLs". I know more or less the sort of thing you are trying to say but any correspondence but the one in my comment to the question is complex and so is a clear explanation. Although I would welcome one. Exactly what are being grouped how by the circles?

2018年07月23日25分08秒

I will say that while this doesn't work for me nearly as well as the Venn diagrams, I appreciate that people vary and learn differently and this is a very well presented explanation unlike any I've seen before, so I support ypercube in awarding the bonus points. Also good work explaining the difference of putting additional conditions in the JOIN clause vs the WHERE clause. Kudos to you, Martin Smith.

2018年07月23日25分08秒

OldPro The Venn diagrams are OK as far as they go I suppose but they are silent on how to represent a cross join, or to differentiate one kind of join predicate such as equi join from another. The mental model of evaluating the join predicate on each row of the cross join result then adding back in unmatched rows if an outer join and finally evaluating the where works better for me.

2018年07月23日25分08秒

The Venn diagrams are good for representing Unions and Intersections and Differences but not joins. They have some minor educational value for very simple joins, i.e. joins where the joining condition is on unique columns.

2018年07月23日25分08秒

Arth - Nope you're wrong. SQL Fiddle sqlfiddle.com/#!3/9eecb7db59d16c80417c72d1/5155 this is something the Venn diagrams can't illustrate.

2018年07月23日25分08秒

MartinSmith Wow, I agree, I'm totally wrong! Too used to working with one-to-manys.. thanks for the correction.

2018年07月23日25分08秒

REGULAR JOIN and INNER JOIN are the same thing. What Graham Ellis wanted to say whit REGULAR is that INNER JOIN is the "default" JOIN, when none of LEFT or RIGHT keywords were specified. If you read the whole post, in the end He says "An INNER JOIN does a full join, just like the first example, and the word OUTER may be added after the word LEFT or RIGHT in the last two examples - it's provided for ODBC compatibility and doesn't add an extra capabilities."

2018年07月23日25分08秒

For those who are confused by pid and spid, pid stands for "person id", and spid stands for "sale property id".

2018年07月23日25分08秒

What if we only want to select Mr.BROWN,Miss SMITH AND Mr Pullen ?

2018年07月23日25分08秒

Fortune Select (rows having name equal to one of) them from what? How is this related to the difference between inner & outer joins?

2018年07月23日25分08秒

many answer don't think about one--multiplicity situation,and this will mislead someone ,your answer is better!

2018年07月24日25分08秒

What is the name of tool? I find it is interesting as it shows number of rows and venn-diagrams

2018年07月23日25分08秒

GrijeshChauhan Datamartist :)

2018年07月23日25分08秒

Trushar :( it is not for Linux system..

2018年07月23日25分08秒

GrijeshChauhan Yeah But you can Try to run it using wine .

2018年07月23日25分08秒

Ohh! yes I ..I used SQLyog using wine.. there is also PlayOnLinux

2018年07月23日25分08秒

nomen Not that this answer addresses it, but INNER JOIN is an intersection and FULL OUTER JOIN is the corresponding UNION if the left & right sets/circles contain the rows of (respectively) LEFT & RIGHT join. PS This answer is unclear about rows in input vs output. It confuses "in the left/right table" with "has a left/right part in the left/right" and it uses "matched row" vs "all" to mean row extended by row from other table vs by nulls.

2018年07月23日25分08秒

I appreciate the simple yet realistic example. I changed a request like SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c, categories_description cd WHERE c.id = cd.categories_id AND c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC to SELECT c.id, c.status, cd.name, c.parent_id, cd.description, c.image FROM categories c INNER JOIN categories_description cd ON c.id = cd.categories_id WHERE c.status = 1 AND cd.language_id = 2 ORDER BY c.parent_id ASC (MySQL) with success. I wasn't sure about the additional conditions, they mix well...

2018年07月23日25分08秒

The result has nothing to (do per se) with primary/unique/candidate keys & foreign keys. The baviour can and should be described without reference to them. A cross join is calculated, then rows not matching the ON condition are filtered out; additionally for outer joins rows filtered/unmatched rows are extended by NULLs (per LEFT/RIGHT/FULL and included.

2018年07月23日25分08秒

You can add to your note, that there is no full outer join in MySQL either.

2018年07月23日25分08秒

It can't possibly be "good practice" to use one type of join over another. Which join you use determines the data that you want. If you use a different one you're incorrect. Plus, in Oracle at least this answer is completely wrong. It sounds completely wrong for everything and you have no proof. Do you have proof?

2018年07月23日25分08秒

1. I mean try to use. I saw lots of people using LEFT OUTER joins everywhere without any good reason. (The joined columns were 'not null'.) In those cases it would be definitely better to use INNER joins. 2. I have added a link explaining the non-associative behaviour better than I could.

2018年07月23日25分08秒

As I know INNER JOIN is slower than LEFT JOIN in most of the times, And people can use LEFT JOIN instead of INNER JOIN by adding a WHERE for removing unexpected NULL results ;).

2018年07月23日25分08秒

These comments made me a bit uncertain. Why do you think INNER is slower?

2018年07月23日25分08秒

I'm not agree with RIGHT OUTER JOIN and means opposite of LEFT JOIN, I think opposite of LEFT JOIN is show each records from left table regardless of the not existence of matching in the right table is you said in LEFT JOIN ;).

1970年01月01日00分03秒

The most precise and concise definitions of LEFT JOIN and RIGHT JOIN.

2018年07月23日25分08秒

Question is asking for Difference between INNER and OUTER joins though, not necessarily left outer join lol

2018年07月23日25分08秒

This is brilliant and explains why join doesn't work as expected for Time Series index's. Time stamps one second apart are unmatched.

2018年07月23日25分08秒

yeliabsalohcin You don't explain "as expected" here or "works" in your comment on the question. It's just some unexplained personal misconception you strangely expect others to have. If you treat words as sloppily when you are reading--misinterpreting clear writing and/or accepting unclear writing--as when you are writing here then you can expect to have misconceptions. In fact this answer like most here is unclear & wrong. "Inner Join: Returns matched records from both tables" is wrong when input column sets differ. It's trying to say a certain something, but it isn't. (See my answer.)

2018年07月23日25分08秒

good and simpler to understand.

2018年07月23日25分08秒

This is both wrong and unclear. Join is not an intersection unless the tables have the same columns. Outer joins don't have rows from A or B unless they have the same columns, in which case there are not nulls added. You are trying to say something, but you are not saying it. You are not explaining correctly or clearly.

2018年07月23日25分08秒

philipxy: Disagreed on your statement Join is not an intersection unless the tables have the same columns No. You can join any columns that you want and if the value match, they will join together.

2018年07月23日25分08秒

That comment is as unclear as your answer. (I suppose you might be thinking something like, the set of subrow values for the common columns of the result is the intersection of the sets of subrow values for the common columns of each of the inputs; but that's not what you have written. You are not clear.)

2018年07月23日25分08秒

This is only correct when the tables have the same column set. (It confuses inner join with intersection & full join with union.) Also "match" is undefined. Read my other comments.

2018年07月23日25分08秒