What is the difference between "INNER JOIN" and "OUTER JOIN"?

Question

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

Solution 1

Assuming you're joining on columns with no duplicates, which is a very common case:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

• An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

A    B
-    -
1    3
2    4
3    5
4    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

Solution 2

The Venn diagrams don't really do it for me.

They don't show any distinction between a cross join and an inner join, for example, or more generally show any distinction between different types of join predicate or provide a framework for reasoning about how they will operate.

There is no substitute for understanding the logical processing and it is relatively straightforward to grasp anyway.

1. Imagine a cross join.
2. Evaluate the on clause against all rows from step 1 keeping those where the predicate evaluates to true
3. (For outer joins only) add back in any outer rows that were lost in step 2.

(NB: In practice the query optimiser may find more efficient ways of executing the query than the purely logical description above but the final result must be the same)

I'll start off with an animated version of a full outer join. Further explanation follows.

---

Explanation

Source Tables

First start with a CROSS JOIN (AKA Cartesian Product). This does not have an ON clause and simply returns every combination of rows from the two tables.

SELECT A.Colour, B.Colour FROM A CROSS JOIN B

Inner and Outer joins have an "ON" clause predicate.

• Inner Join. Evaluate the condition in the "ON" clause for all rows in the cross join result. If true return the joined row. Otherwise discard it.
• Left Outer Join. Same as inner join then for any rows in the left table that did not match anything output these with NULL values for the right table columns.
• Right Outer Join. Same as inner join then for any rows in the right table that did not match anything output these with NULL values for the left table columns.
• Full Outer Join. Same as inner join then preserve left non matched rows as in left outer join and right non matching rows as per right outer join.

Some examples

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour = B.Colour

The above is the classic equi join.

Animated Version

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON A.Colour NOT IN ('Green','Blue')

The inner join condition need not necessarily be an equality condition and it need not reference columns from both (or even either) of the tables. Evaluating A.Colour NOT IN ('Green','Blue') on each row of the cross join returns.

SELECT A.Colour, B.Colour FROM A INNER JOIN B ON 1 =1

The join condition evaluates to true for all rows in the cross join result so this is just the same as a cross join. I won't repeat the picture of the 16 rows again.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour

Outer Joins are logically evaluated in the same way as inner joins except that if a row from the left table (for a left join) does not join with any rows from the right hand table at all it is preserved in the result with NULL values for the right hand columns.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour IS NULL

This simply restricts the previous result to only return the rows where B.Colour IS NULL. In this particular case these will be the rows that were preserved as they had no match in the right hand table and the query returns the single red row not matched in table B. This is known as an anti semi join.

It is important to select a column for the IS NULL test that is either not nullable or for which the join condition ensures that any NULL values will be excluded in order for this pattern to work correctly and avoid just bringing back rows which happen to have a NULL value for that column in addition to the un matched rows.

SELECT A.Colour, B.Colour FROM A RIGHT OUTER JOIN B ON A.Colour = B.Colour

Right outer joins act similarly to left outer joins except they preserve non matching rows from the right table and null extend the left hand columns.

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON A.Colour = B.Colour

Full outer joins combine the behaviour of left and right joins and preserve the non matching rows from both the left and the right tables.

SELECT A.Colour, B.Colour FROM A FULL OUTER JOIN B ON 1 = 0

No rows in the cross join match the 1=0 predicate. All rows from both sides are preserved using normal outer join rules with NULL in the columns from the table on the other side.

SELECT COALESCE(A.Colour, B.Colour) AS Colour FROM A FULL OUTER JOIN B ON 1 = 0

With a minor amend to the preceding query one could simulate a UNION ALL of the two tables.

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour WHERE B.Colour = 'Green'

Note that the WHERE clause (if present) logically runs after the join. One common error is to perform a left outer join and then include a WHERE clause with a condition on the right table that ends up excluding the non matching rows. The above ends up performing the outer join...

... And then the "Where" clause runs. NULL= 'Green' does not evaluate to true so the row preserved by the outer join ends up discarded (along with the blue one) effectively converting the join back to an inner one.

If the intention was to include only rows from B where Colour is Green and all rows from A regardless the correct syntax would be

SELECT A.Colour, B.Colour FROM A LEFT OUTER JOIN B ON A.Colour = B.Colour AND B.Colour = 'Green'

SQL Fiddle

See these examples run live at SQLFiddle.com.

Solution 3

Joins are used to combine the data from two tables, with the result being a new, temporary table. Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join. The difference between an inner join and an outer join is that an inner join will return only the rows that actually match based on the join predicate. For eg- Lets consider Employee and Location table:

Employee

EmpID	EmpName
13	Jason
8	Alex
3	Ram
17	Babu
25	Johnson

Location

EmpID	EmpLoc
13	San Jose
8	Los Angeles
3	Pune, India
17	Chennai, India
39	Bangalore, India

Inner Join:- Inner join creates a new result table by combining column values of two tables (Employee and Location) based upon the join-predicate. The query compares each row of Employee with each row of Location to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of Employee and Location are combined into a result row. Here’s what the SQL for an inner join will look like:

select  * from employee inner join location on employee.empID = location.empID
OR
select  * from employee, location where employee.empID = location.empID

Now, here is what the result of running that SQL would look like:

Employee.EmpId	Employee.EmpName	Location.EmpId	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India

Outer Join:- An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins and right outer joins, depending on which table's rows are retained (left or right).

Left Outer Join:- The result of a left outer join (or simply left join) for tables Employee and Location always contains all records of the "left" table (Employee), even if the join-condition does not find any matching record in the "right" table (Location). Here is what the SQL for a left outer join would look like, using the tables above:

select  * from employee left outer join location on employee.empID = location.empID;
//Use of outer keyword is optional

Now, here is what the result of running this SQL would look like:

Employee.EmpId	Employee.EmpName	Location.EmpId	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
25	Johnson	NULL	NULL

Note how while Johnson has no entry in the employee location table, he is still included in the results but the location fields are nulled.

Right Outer Join:- A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (Location) will appear in the joined table at least once. If no matching row from the "left" table (Employee) exists, NULL will appear in columns from Employee for those records that have no match in Location. This is what the SQL looks like:

select * from employee right outer join location  on employee.empID = location.empID;
//Use of outer keyword is optional

Using the tables above, we can show what the result set of a right outer join would look like:

Employee.EmpId	Employee.EmpName	Location.EmpId	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
NULL	NULL	39	Bangalore, India

Note how while there are no employees listed as working in Bangalore, it is still included in the results with the employee fields nulled out.

Full Outer Joins:- Full Outer Join or Full Join is to retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. It includes all rows from both tables, regardless of whether or not the other table has a matching value.

Employee.EmpId	Employee.EmpName	Location.EmpId	Location.EmpLoc
13	Jason	13	San Jose
8	Alex	8	Los Angeles
3	Ram	3	Pune, India
17	Babu	17	Chennai, India
25	Johnson	NULL	NULL
NULL	NULL	39	Bangalore, India

MySQL 8.0 Reference Manual - Join Syntax

Oracle Join operations

Solution 4

Inner Join

Retrieve the matched rows only, that is, A intersect B.

SELECT *
FROM dbo.Students S
INNER JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

---

Left Outer Join

Select all records from the first table, and any records in the second table that match the joined keys.

SELECT *
FROM dbo.Students S
LEFT JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

---

Full Outer Join

Select all records from the second table, and any records in the first table that match the joined keys.

SELECT *
FROM dbo.Students S
FULL JOIN dbo.Advisors A
    ON S.Advisor_ID = A.Advisor_ID

---

References

• Inner and outer joins SQL examples and the Join block

• SQL: JOINS

Solution 5

In simple words:

An inner join retrieve the matched rows only.

Whereas an outer join retrieve the matched rows from one table and all rows in other table ....the result depends on which one you are using:

• Left: Matched rows in the right table and all rows in the left table

• Right: Matched rows in the left table and all rows in the right table or

• Full: All rows in all tables. It doesn't matter if there is a match or not

View more solutions

Comments

[philipxy]

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

[philipxy]

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

[philipxy]

My preceding comment is about a confused repudiated Jeff Atwood blog post.

[philipxy]

My 1st comment's link is external, but i.stack.imgur.com has permanent copies of its illustrations of output (not input) for inner, left & full joins (in green).

[softveda]

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.

[Thomas]

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.

[PhiLho]

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

[Ameer]

yes great and excellent explanation. but why in column b the values are not in order? i.e it is 6,5 not as 5,6?

[Mark Harrison]

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

[codetaku]

Thomas: Actually, you and Mark are both wrong about a different part of the phrasing. "The outer parts of a venn diagram" refers to "A U B \ A ∩ B". So Mark accidentally directly contradicted himself, though the remainder of his explanation was obviously clear enough to make up for that.

[Birupakhya Dash]

To say that outer join is a union may sound a little confusing when the result set of A left outer join B doesn't have the non-matching elements of B. Isn't it?

[Grijesh Chauhan]

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

[Tushar Gupta - curioustushar]

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

[Grijesh Chauhan]

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

[jdero]

"Assuming you're joining on columns with no duplicates, which is a very common case:" - Does much change when this is not the case?

[nomen]

A join doesn't give you the union or intersection of anything. It gives you a closure in a lattice.

[Old Pro]

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.

[Martin Smith]

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

[ypercubeᵀᴹ]

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.

[Ben]

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?

[Lajos Veres]

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.

[luk2302]

is there a way to get the two green rows and the one blue row of A without using distinct?

[Martin Smith]

@luk2302 - Yes, you need a semi join. There is no semi join keyword in SQL though. Instead you can use in or exists. i.e. SELECT A.Colour FROM A WHERE A.Colour IN (SELECT B.Colour FROM B) or alternatively SELECT A.Colour FROM A WHERE EXISTS (SELECT * FROM B WHERE A.Colour = B.Colour)

[luk2302]

thanks, i already ended up using where exists and it works. But I hoped there was a way to achieve that with just a regular join. Thanks anyway! And btw: far better representation of joins than by venn diagrams :)

[shA.t]

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

[Lajos Veres]

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

[potashin]

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

[philipxy]

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.

[philipxy]

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

[philipxy]

The + syntax was never ANSI, it was Oracle, and it was always inadequate for expressions using multiple tables or comparisons. The ON syntax is ANSI. PS The comparison to Venn diagrams is phrased wrong. See my comment on the question.

[LearnByReading]

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

[Arth]

Pretty sure a lot of your JOIN results have too many green rows.. that first INNER JOIN should only have 2 green rows for example. In fact pretty much all the cases where you have 4 green rows consecutively should all only be 2 rows.

[Martin Smith]

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

[philipxy]

Here is the correct statement for intersection/union of circles/sets A & B in terms of tables A & B. Circle A = A LEFT JOIN B ON condition. Circle B = (A RIGHT JOIN B ON condition). (A FULL OUTER JOIN B on condition) = Circle A UNION Circle B. (A INNER JOIN B ON *condition) = Circle A INTERSECT Circle B. Circle A MINUS Circle B = (A rows not matched by A JOIN B extended by NULLs). Circle B MINUS Circle A = (B rows not matched by A JOIN B extended by NULLs). See my comment (the first) on the question.

[philipxy]

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.

[SuicideSheep]

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

[philipxy]

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

[philipxy]

@HelenNeely Unfortunately this explanation is wrong. Its bullets are only correct when all the columns are the same, and its language is fuzzy. See my comment immediately before yours, and my comment on the question.

[philipxy]

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.

[philipxy]

Unfortunately, your illustrations don't illustrate of LEFT vs INNER JOIN ON in the general case. And although most of the Venn diagrams here are also limited to illustrating special cases, a Venn diagram, when properly interpreted, can represent inner vs outer join, with the intersection holding your green-green INNER JOIN rows. See my other comments.

[yeliabsalohcin]

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

[Basil Bourque]

I re-created these excellent examples on SQLFiddle.com for anyone to try running. Uncomment any of the queries you wish to run.

[philipxy]

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

[Git Gud]

How did you do these animations? Great answer, the only bit I dislike is your modesty in saying that the Venn diagrams don't do it for you. The reality is that they are insufficient to model what's going on and this is important to tell, lest people get the wrong idea.

[philipxy]

Your text is unclear & wrong. The "matched rows only" are rows from the cross join of A & B & what is retrieved (A inner join B) is not A intersect B but (A left join B) intersect (A right join B). The "selected" rows are not from A & B, they are from A cross join B & from null-extended values of rows from A & B.

[zr00]

@GitGud I'd contest that the Venn diagrams, while incomplete, supplement this answer for me. Like what was said earlier, people have different learning styles.

[Chris]

@Damian yes, OUTER JOIN and FULL OUTER JOIN are equivalent, along with LEFT/RIGHT JOIN are equivalent to LEFT/RIGHT OUTER JOIN, in the same way INNER JOIN is equivalent to a simple JOIN

[philipxy]

@zarose The answers using Venn diagrams are unclear, incomplete & wrong, including in their use of Venn diagrams. See my first comment on the question & elsewhere on this page. Tolerating such writing is not a learning style. It's clearly a writing style. And (from the upvotes & comments) a reading style.

[onedaywhen]

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

[philipxy]

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.

[onedaywhen]

I don't want Venn diagrams either!

[philipxy]

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.

[onedaywhen]

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!

[onedaywhen]

@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

[philipxy]

In the case of 'relational Cartesian product', that is a standard & reasonable name for a certain thing that people do generally understand & which is reasonably described as similar to a Cartesian product. In the case of SQL Venn diagrams, they don't make sense, people just assume they do, whether they do or don't understand the operators/differences.

[mckenzm]

Depends upon the engine. gnu join, joinkeys, DB2, MySQL. Performance traps abound, such as loose typing or an explicit cast.

[Joey]

best answer so far, alternative syntax - that's what I've been looking for, thanks!

[Solomon Ucko]

Is INNER JOIN ON basically the same as CROSS JOIN WHERE?

[Martin Smith]

@SolomonUcko - yes. An inner join written in ANSI 89 style (before JOIN keyword was introduced) is exactly that. Though it uses the comma operator to denote the CROSS JOIN

[Manuel Jordan]

@TusharGupta-curioustushar you should include the "Tables used for SQL Examples"

[philipxy]

The Venn diagrams are mislabelled. See my comments on the question & other answers. Also most of this language is poor. Eg: "When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of Employee and Location are combined into a result row." No, not "When the join-predicate is satisfied by matching non-NULL values". Values in rows don't matter other than whether the condition as a whole being true or false. Some values could well be NULL for a true condition.

[Martin Smith]

An outer join result is the same as inner join but plus some additional rows so I have no idea why you think outer join would be faster. Also what are these "two types" of inner join? I suppose you are referring to full,left, and right for outer?

[philipxy]

Outer join is not faster than inner join.

[philipxy]

What I meant was that join is only an intersection of inputs when it is a natural inner join of inputs with the same columns. You are using the words "intersection" & "union" wrongly.

[Colm Bhandal]

Pity this answer is not top of the list. The problem with Venn diagrams is that they portray a confusing message. They make it looks like we're working with the intersection of sets of rows from both tables, which is not true. Like you illustrate, we're working with the caretesian product of both sets, to start with, and then we filter down and add back in any outer elements if necessary. Great explanation and great diagrams.

[Colm Bhandal]

I have downvoted this because it is wrong. Please consider removing the answer as it will mislead generations of computer science students who are fooled by the large upcote count. Venn diagrams do not explain join. The inner part of a join is not intersection.

[Colm Bhandal]

"Retrieve the matched rows only, that is, A intersect B." This is wrong.

[Colm Bhandal]

The assumption that SQL joins are always a match on primary/foreign keys is leading to this misuse of Venn diagrams. Please revise your answer accordingly.

[philipxy]

@Persistence What is needed for the tables is text that is table initialization code formatted in columns that is suitable to copy & paste & run.