标签云

微信群

扫码加入我们

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.)

2019年05月24日54分26秒

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-joinsFor 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.

2019年05月24日54分26秒

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.

2019年05月24日54分26秒

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.

2019年05月23日54分26秒

the original title was clearer as to what was beng asked ("different join types"could be referring to physical join types such as hash and merge for example) The original title was also clearly effective with search engines from the number of views. I've rolled that unneeded edit back

2019年05月23日54分26秒

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.

2019年05月24日54分26秒

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.

2019年05月24日54分26秒

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 :)

2019年05月23日54分26秒

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

2019年05月23日54分26秒

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

2019年05月24日54分26秒

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.

2019年05月23日54分26秒

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.

2019年05月24日54分26秒

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.

2019年05月23日54分26秒

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

2019年05月23日54分26秒

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

2019年05月24日54分26秒

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

2019年05月24日54分26秒

GrijeshChauhan Datamartist :)

2019年05月23日54分26秒

Trushar :( it is not for Linux system..

2019年05月24日54分26秒

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

2019年05月23日54分26秒

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

2019年05月23日54分26秒

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.

2019年05月24日54分26秒

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 JOINcategories_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...

2019年05月24日54分26秒

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.

2019年05月23日54分26秒

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

2019年05月23日54分26秒

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 thisanswer is completely wrong. It sounds completely wrong for everything and you have no proof. Do you have proof?

2019年05月24日54分26秒

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.

2019年05月23日54分26秒

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 ;).

2019年05月23日54分26秒

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

2019年05月24日54分26秒

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

2019年05月24日54分26秒

LearnByReading: my picture on the right is a right outer join i.e. replace TableA a LEFT OUTER JOIN TableB b with TableB B RIGHT OUTER JOIN TableA a

2019年05月24日54分26秒

good and simpler to understand.

2019年05月24日54分26秒

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

2019年05月24日54分26秒

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.)

2019年05月23日54分26秒

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.

2019年05月24日54分26秒

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.

2019年05月24日54分26秒

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.

2019年05月23日54分26秒

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.)

2019年05月24日54分26秒

I have indeed read your many comments. When you say, "a Venn diagram, when properly interpreted, can represent inner vs outer join" do you mean when properly interpreted by the observer or the Venn diagram itself? If the latter, please draw it :)

2019年05月24日54分26秒

I'm not sure what you are trying to say. I am talking about the standard interpretation of a Venn diagram as sets of elements. (Because some uses of diagrams don't even manage that.) "Properly" for an application includes saying what the sets and/or elements are. See comment at the top of this page with 50 upvotes re a Venn diagram for inner vs outer joins. I'll edit some of my comments into this question. I don't want a Venn diagram in this post.

1970年01月01日00分03秒

I don't want Venn diagrams either!

2019年05月24日54分26秒

I must admit that, despite my quick phrasing in comments, because SQL involves bags & nulls and SQL culture doesn't have common terminology to name & distinguish between relevant notions, it is non-trivial even to explain clearly how elements of a Venn diagram are 1:1 with output "rows", let alone input "rows". Or what inner or outer joins do, let alone their difference. "value" may or may not include NULL, "row" may be a list of values vs a slot in a table value or variable & "=" may be SQL "=" vs equality.

2019年05月24日54分26秒

Similar to our Cartesian-product-vs-relational-product discussion, I suspect it is the case that the Venn diagrams make a lot of sense to folk who already understand the differences between the join types!

2019年05月23日54分26秒

Nothing in this clearly describes what either join does. (Nor does it address a "difference" between them, other than saying they are different.) This adds nothing to the many answers (many highly upovoted) of a10-yr old question--is it "helpful"?

2019年05月23日54分26秒